How to Pivot Data in MySQL Using `CASE` or `IF` Statements?

Displaying Data in Pivot Format with GROUP BY

The task at hand involves transforming a relational database table with rows representing individual data points into a pivot format where data is organized into columns based on timestamps and values are grouped by a specific identifier.

To achieve this pivot structure, we can employ the CASE statement in MySQL. This statement allows us to conditionally assign values based on different conditions:

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

In this query, we use the SUM function to aggregate the data_value for each distinct data_timestamp. The CASE statement checks whether the data_id matches a specific value (e.g., 1, 2, or 20). If it matches, the corresponding data_value is included in the summation for the respective column (e.g., input_1, input_2, input_20). If no match is found, zero is used in the calculation. This results in a table where data_timestamp is the row identifier, and each data_id has a dedicated column displaying its corresponding aggregated data_value.

We can also utilize the IF statement instead of CASE for the same purpose:

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

Both the CASE and IF approaches allow us to dynamically generate the necessary columns and aggregate the values based on the data_id and data_timestamp, ultimately presenting the data in the desired pivot format.

The above is the detailed content of How to Pivot Data in MySQL Using `CASE` or `IF` Statements?. For more information, please follow other related articles on the PHP Chinese website!