How to Pivot Rows into Columns in MySQL Using CASE and Aggregate Functions?

How to Transform Rows to Columns in MySQL

Pivot tables are a common data transformation used to rearrange data into a format that is easier to read and analyze. In this scenario, we want to transform a result set with multiple rows into a table with columns for each distinct Type value and rows for each unique ID and Email combination.

To achieve this in MySQL, we can use the following query:

SELECT ID, 
 MAX(CASE Type WHEN 202 THEN Degignation END) AS `202`
 MAX(CASE Type WHEN 234 THEN Degignation END) AS `234`
 MAX(CASE Type WHEN 239 THEN Degignation END) AS `239`
 Email
FROM mytable
GROUP BY ID, Email

This query uses the CASE statement to conditionally set the Degignation value for each row based on its Type value. The MAX() aggregate function then returns the maximum Degignation value for each Type within each group of ID and Email.

Note: It's important to replace the placeholder table name (mytable) with the actual table name containing your data. Additionally, you need to know all the distinct Type values in advance, as SQL does not allow dynamic addition of columns during query execution.

The above is the detailed content of How to Pivot Rows into Columns in MySQL Using CASE and Aggregate Functions?. For more information, please follow other related articles on the PHP Chinese website!