Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql query to dynamically convert rows to columns

Can MySQL convert columns into rows, dynamically adding as many columns as are needed for the rows. I think my question might be related to pivot tables but I'm unsure and I don't know how to frame this question other than by giving the following example.

Given a two tables A and B, which look like

Table A

+--+-----+----+
|id|order|data|
+--+-----+----+
|1 |1    |P   |
+--+-----+----+
|2 |2    |Q   |
+--+-----+----+
|2 |1    |R   |
+--+-----+----+
|1 |2    |S   |
+--+-----+----+

I like to write a query that looks like the following:

Result Table

+--+-----+-----+
|id|data1|data2|
+--+-----+-----+
|1 |P    |S    |
+--+-----+-----+
|2 |R    |Q    |
+--+-----+-----+

Basically I want to turn each row in table B into a column in the result table. If there was a new entry was added to table B for id=1, then I want the result table to automatically extend by one column to accommodate this extra data point.

like image 953
Dom Avatar asked Feb 12 '13 14:02

Dom


2 Answers

You can use GROUP BY and MAX to simulate pivot. MySQL also supports IF statement.

SELECT  ID,
        MAX(IF(`order` = 1, data, NULL)) data1,
        MAX(IF(`order` = 2, data, NULL)) data2
FROM    TableA
GROUP   BY ID
  • SQLFiddle Demo

If you have multiple values of order, dynamic SQL may be more appropriate so that you will not have to modify the query:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(`order` = ', `order`, ',data,NULL)) AS data', `order`)
  ) INTO @sql
FROM TableName;

SET @sql = CONCAT('SELECT  ID, ', @sql, ' 
                  FROM    TableName
                  GROUP   BY ID');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
  • SQLFiddle Demo
  • SQLFiddle Demo (Another example)

OUTPUT OF BOTH QUERIES:

╔════╦═══════╦═══════╗
║ ID ║ DATA1 ║ DATA2 ║
╠════╬═══════╬═══════╣
║  1 ║ P     ║ S     ║
║  2 ║ R     ║ Q     ║
╚════╩═══════╩═══════╝
like image 132
John Woo Avatar answered Nov 12 '22 16:11

John Woo


You need to use MAX and GROUP BY to simulate a PIVOT:

SELECT Id,
   MAX(CASE WHEN Order = 1 THEN data END) data1,
   MAX(CASE WHEN Order = 2 THEN data END) data2
FROM TableA
GROUP BY Id

And here is the SQL Fiddle.

like image 37
sgeddes Avatar answered Nov 12 '22 15:11

sgeddes