mysql pivot query results with GROUP BY

by

PIVOTing is neither easy (nor nice) to do. I prefer using CASE:

SELECT d.data_timestamp
     , SUM( CASE WHEN data_id =  1 THEN data_value ELSE 0 END ) AS 'input_1'
     , SUM( CASE WHEN data_id =  2 THEN data_value ELSE 0 END ) AS 'input_2'
     ...
     , SUM( CASE WHEN data_id = 20 THEN data_value ELSE 0 END ) AS 'input_20'
FROM data 
GROUP BY data_timestamp
ORDER BY data_timestamp ASC

but IF works in MySQL as well:

SELECT d.data_timestamp
     , SUM( IF(data_id =  1, data_value, 0) ) AS 'input_1'
     , SUM( IF(data_id =  2, data_value, 0) ) AS 'input_2'
     ...
     , SUM( IF(data_id = 20, data_value, 0) ) AS 'input_20'
FROM data 
GROUP BY data_timestamp
ORDER BY data_timestamp ASC

Alternatively, you can use a 20-level JOIN:

SELECT d.data_timestamp
     , d01.data_value AS 'input_1'
     , d02.data_value AS 'input_2'
     ...
     , d20.data_value AS 'input_20'
FROM
  ( SELECT DISTINCT d.data_timestamp
    FROM data
  ) AS d 
  LEFT JOIN data AS d01
    ON d01.data_timestamp = d.data_timestamp
    AND d01.data_id =  1
  LEFT JOIN data AS d02
    ON d02.data_timestamp = d.data_timestamp
    AND d02.data_id =  2
  ...                                   --- 20 JOINs
  LEFT JOIN data AS d20
    ON d20.data_timestamp = d.data_timestamp
    AND d20.data_id = 20
ORDER BY d.data_timestamp ASC

More Related Contents:

  1. What is the opposite of GROUP_CONCAT in MySQL?
  2. MySQL pivot row into dynamic number of columns
  3. SQL split values to multiple rows
  4. How do I import CSV file into a MySQL table?
  5. Import data in MySQL from a CSV file using LOAD DATA INFILE
  6. MySQL load NULL values from CSV data
  7. How to pivot a MySQL entity-attribute-value schema
  8. How to import a CSV file into a MySQL table
  9. mysql pivot/crosstab query
  10. MySQL: Split comma separated list into multiple rows
  11. Importing a csv into mysql via command line
  12. Mysql string split [duplicate]
  13. MySQL dynamic-pivot
  14. Dump all tables in CSV format using ‘mysqldump’
  15. Mysql Convert Column to row (Pivot table )
  16. Mysql query to dynamically convert rows to columns on the basis of two columns
  17. Import CSV to MySQL
  18. How to transpose mysql table rows into columns
  19. LOAD DATA LOCAL INFILE gives the error The used command is not allowed with this MySQL version
  20. Importing a CSV to MySQL with different date format
  21. How to export data from SQL Server 2005 to MySQL [closed]
  22. Dump a mysql database to a plaintext (CSV) backup from the command line
  23. How can I correct MySQL Load Error
  24. MySQL Pivot Table Column Data as Rows
  25. How to insert selected columns from a CSV file to a MySQL database using LOAD DATA INFILE
  26. Transpose rows into columns in MySQL
  27. Import CSV to Update only one column in table
  28. Join two tables (with a 1-M relationship) where the second table needs to be ‘flattened’ into one row
  29. How I can Import data from CSV to MySQL?
  30. Import CSV to Update rows in table

Leave a Comment