How to Pivot MySQL Query Results with GROUP BY and Multiple Data IDs?

MySQL PIVOT Query Results with GROUP BY

Problem:

Transforming a database table to display rows as columns, grouping data by a specified key (e.g., timestamp) while ensuring that all data values for each key are presented in the corresponding column.

Proposed Query:

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 d.data_timestamp
ORDER BY d.data_timestamp ASC

Explanation:

• The outer query selects distinct data timestamps from the data table.
• For each data timestamp, the query calculates the sum of data values for each data_id using the IF() function inside the SUM() aggregate.
• If the data_id matches the desired id (e.g., 1 for input_1), the corresponding data value is added to the sum; otherwise, 0 is added.
• This calculation is repeated for each data_id up to 20, creating a column for each different data_id.
• The final result is grouped by the data timestamp and ordered in ascending order.

Limitations:

• This approach requires an explicit sum for each data_id in the query, which may not be feasible if there are a large number of ids.
• An alternative approach involving a CASE or multi-level JOINs can be used depending on the specific requirements and data structure.

The above is the detailed content of How to Pivot MySQL Query Results with GROUP BY and Multiple Data IDs?. For more information, please follow other related articles on the PHP Chinese website!