How to Pivot Query Results in MySQL Using GROUP BY?

MySQL PIVOTING Query Results with GROUP BY

Objective

Transform rows to columns in a MySQL table using the PIVOT or GROUP BY technique.

Background

PIVOTING, a data transformation technique, rearranges row data into columns. In this case, the goal is to group data by a specific column, such as timestamp, and display each data value as a separate column.

Using CASE Statements

Instead of the traditional PIVOT syntax, which is not widely supported in MySQL, the SUM() function can be used in conjunction with CASE statements to achieve the desired result.

<code class="sql">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</code>

Using IF Statements

Alternatively, IF statements can be used within the SUM() function to achieve the same result.

<code class="sql">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</code>

Using Multiple JOIN Statements

Another approach is to use multiple JOIN statements to create the desired output.

<code class="sql">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</code>

By utilizing these techniques, you can easily pivot query results in MySQL using GROUP BY.

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