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.

score 62 · Accepted Answer · edited Jul 27 '16 at 18:16

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 '16 at 18:16

reformed

4,505
11
62
88

answered Feb 12 '13 at 14:02

John Woo

258,903
69
498
492

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. – Dom Feb 12 '13 at 14:35
the one you with `@` prefix are called user variables. and here, http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html, for learning `PreparedStatements`. – John Woo Feb 12 '13 at 14:38
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 – Moon Jul 31 '13 at 06:48
Hello I found your answer interesting but how can we implement it using mysql view. Maybe you can help this question https://stackoverflow.com/questions/46324088/how-to-covert-dynamic-rows-into-columns-in-mysql-view – Yves Gonzaga Sep 20 '17 at 13:59
@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 – Elena Politi Apr 20 '18 at 12:10
@ElenaPoliti I only edited the question; I didn't create the fiddle. Post another comment directed at @ JohnWoo and he should get a notification. – reformed Apr 20 '18 at 18:33
@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 – Elena Politi Apr 20 '18 at 21:43
Is it possible to join '@sql with any table @JohnWoo – selvakumar Sep 21 '19 at 13:29
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. – Sergio May 11 '21 at 05:34

score 6 · Answer 2 · answered Feb 12 '13 at 14:02

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 '13 at 14:02

sgeddes

62,311
6
61
83

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 Feb 12 '13 at 14:34
@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. – sgeddes Feb 12 '13 at 14:39
2

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. – Dom Feb 12 '13 at 17:09
@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... – sgeddes Feb 12 '13 at 17:17
Oh, sorry, I've tried to undo the downvote but because it was cast over three hours ago I cannot – Dom Feb 12 '13 at 17:53

Mysql query to dynamically convert rows to columns

2 Answers2

Linked

Related