Mysql query to dynamically convert rows to columns

Question

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.

Solution 1

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     ║
╚════╩═══════╩═══════╝

Solution 2

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.

Comments

[Dom]

This doesn't work as its limited to two columns and can't handle dynamically adding extra columns as the number or rows increase. Thanks for trying though

[Dom]

Fantastic, second example is exactly what I asked for. Unfortunately for me there is a lot of new SQL syntax to get my head around.

[John Woo]

the one you with @ prefix are called user variables. and here, dev.mysql.com/doc/refman/5.0/en/…, for learning PreparedStatements.

[sgeddes]

@Dom -- no worries -- dynamic sql is your best bet if you don't know the maximum number of Orders. If it were 10, then just add 10 MAX statements :) -- Glad we could help nonetheless.

[Dom]

I was the downvoter. I'm new to stackoverflow and I thought this was what I was supposed to do. Because I'd asked about dynamically adding columns and also included the bit about automatically extending the columns to fit new data, I thought your answer wasn't a good starting point for anyone else stuck in the same way I was. If you really feel my downvote was unnecessary, then let me know and I'll remove it.

[sgeddes]

@Dom -- I honestly didn't read your note about dynamically adding columns before I wrote my answer -- I saw your table and your desired results. By the time I read you wanted dynamic columns, others had already answered. I generally only downvote answers that are flat out wrong -- my answer shows how to pivot a table in MySQL and could be useful to other readers for that purpose. No worries though, just trying to help out. Best of luck...

[Dom]

Oh, sorry, I've tried to undo the downvote but because it was cast over three hours ago I cannot

[Moon]

The above solution is working great but my scenario is that instead of applying pivot on one Column on basis of another I also have another columns on which I want to apply pivot on basis of another column. Its like i apply two separate pivots on two columns of a table and join there result. Any Help

[Yves Gonzaga]

Hello I found your answer interesting but how can we implement it using mysql view. Maybe you can help this question stackoverflow.com/questions/46324088/…

[Elena Politi]

@reformed I tried your fiddle but it seems not working as it gives me an empty set. The solution is exactly what I am looking for, but I am not very well prepared on MYSQL and don't know if I do something wrong

[reformed]

@ElenaPoliti I only edited the question; I didn't create the fiddle. Post another comment directed at @ JohnWoo and he should get a notification.

[Elena Politi]

@JohnWoo I tried your fiddle but it seems not working as it gives me an empty set. The solution is exactly what I am looking for, but I am not very well prepared on MYSQL and don't know if I do something wrong

[selvakumar]

Is it possible to join '@sql with any table @JohnWoo

[Sergio]

In my case, I needed about 45 columns, so the CONCAT was not getting all the results. This is solved by editing the global variable: group_concat_max_len (set global and set session). Lucky for me, I'll be using java, so I won't have to worry about this.