How can I pivot query results in MySQL using the GROUP BY clause?

Pivoting Query Results using GROUP BY in MySQL

By restructuring data from rows into columns, pivoting can help improve data visualization and analysis. To pivot query results based on a specific column, we can use the GROUP BY clause in MySQL.

Consider a sample table with the following schema:

data_id     data_timestamp         data_value
--------------------------------------------
1           2011-07-07 00:01:00    0.400  
1           2011-07-07 00:02:00    0.500
1           2011-07-07 00:03:00    0.600
1           2011-07-07 00:04:00    0.700
2           2011-07-07 00:01:00    0.100  
2           2011-07-07 00:02:00    0.200
2           2011-07-07 00:03:00    0.250
2           2011-07-07 00:04:00    2.300

To pivot this data based on the data_timestamp column, we can use a combination of the CASE statement and the SUM aggregate function:

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'
FROM data 
GROUP BY data_timestamp
ORDER BY data_timestamp ASC

This query effectively summarizes the data_value column for each unique data_timestamp and creates new columns for each distinct data_id (in this case, input_1 and input_2).

Alternatively, we can also use the IF statement to achieve the same result:

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'
FROM data 
GROUP BY data_timestamp
ORDER BY data_timestamp ASC

Both of these methods provide an effective way to pivot query results using the GROUP BY clause in MySQL, allowing for more flexible and informative data analysis.

The above is the detailed content of How can I pivot query results in MySQL using the GROUP BY clause?. For more information, please follow other related articles on the PHP Chinese website!