Mysql query to dynamically convert rows to columns

by

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

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

More Related Contents:

  1. How can I return pivot table output in MySQL?
  2. Using LIMIT within GROUP BY to get N results per group?
  3. Error related to only_full_group_by when executing a query in MySql
  4. MySQL Query GROUP BY day / month / year
  5. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  6. Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs
  7. Grouping into interval of 5 minutes within a time range
  8. Retrieving the last record in each group – MySQL
  9. What’s faster, SELECT DISTINCT or GROUP BY in MySQL?
  10. MySQL “Group By” and “Order By”
  11. SELECT / GROUP BY – segments of time (10 seconds, 30 seconds, etc)
  12. mysql pivot/crosstab query
  13. MySQL dynamic-pivot
  14. In SQL, how to select the top 2 rows for each group
  15. Mysql Convert Column to row (Pivot table )
  16. Mysql query to dynamically convert rows to columns on the basis of two columns
  17. SQL Query to get column values that correspond with MAX value of another column?
  18. How to transpose mysql table rows into columns
  19. MySQL Group By and Sum total value of other column
  20. Is it possible to GROUP BY multiple columns using MySQL?
  21. Setting column values as column names in the SQL query result
  22. Using Count to find the number of occurrences
  23. What does SQL clause “GROUP BY 1” mean?
  24. Using ORDER BY and GROUP BY together
  25. MySQL: How do I join same table multiple times?
  26. MySQL Pivot Table Column Data as Rows
  27. MySQL GROUP BY DateTime +/- 3 seconds
  28. MySQL Group By Hours
  29. mysql count group by having
  30. SQL query with avg and group by

Leave a Comment