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.

reformed · Accepted Answer · 2016-07-27 18:16:07Z

62

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;

OUTPUT OF BOTH QUERIES:

╔════╦═══════╦═══════╗
║ ID ║ DATA1 ║ DATA2 ║
╠════╬═══════╬═══════╣
║  1 ║ P     ║ S     ║
║  2 ║ R     ║ Q     ║
╚════╩═══════╩═══════╝

edited Jul 27, 2016 at 18:16

reformed

4,82813 gold badges69 silver badges94 bronze badges

answered Feb 12, 2013 at 14:02

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Dom Over a year ago

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 Over a year ago

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

Moon Over a year ago

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 Over a year ago

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 Over a year ago

@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

|

sgeddes · Accepted Answer · 2013-02-12 14:02:34Z

6

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.

answered Feb 12, 2013 at 14:02

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

5 Comments

Dom Over a year ago

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

sgeddes Over a year ago

@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 Over a year ago

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 Over a year ago

@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 Over a year ago

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

Collectives™ on Stack Overflow

Mysql query to dynamically convert rows to columns

2 Answers 2

9 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related