Transform rows to columns in a MySQL table using the PIVOT or GROUP BY technique.
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.
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>
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>
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.
以上是如何使用 GROUP BY 在 MySQL 中透視查詢結果?的詳細內容。更多資訊請關注PHP中文網其他相關文章!