How to Pivot Rows into Columns in MySQL Using CASE and MAX()?

Transforming Rows to Columns in MySQL

Consider a database with the following table:

CREATE TABLE mytable (
  ID INTEGER PRIMARY KEY,
  Type INTEGER,
  Email VARCHAR(255),
  Degignation VARCHAR(255)
);

The goal is to transform the data from rows to columns, with the Type column becoming column headers and the last column becoming row values. The expected output would look like this:

ID                202         234                     239        Email
1000000000   Entrepreneur  Engineering,Development    CTO       [email protected]

To achieve this transformation, MySQL provides a rather complex 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 utilizes the CASE expression to check each row's Type column and assign the corresponding Degignation value to the appropriate column in the result set. The MAX() function is used to ensure that only the latest (or maximum) value is returned for each Type column.

It's important to note that the query requires a fixed set of column headers, meaning that the distinct Type values must be known in advance. SQL does not support dynamic column addition based on discovered data values during query execution.

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