How to Transform a MySQL Result Set into a Pivot Table Using Conditional Aggregation?

Pivot Table Transformation in MySQL

You have a result set in MySQL with multiple rows sharing the same ID but different types and designations, and you want to transform it into a pivot table format, where types become columns and designations become row values within those columns. Here's how you can achieve this:

The solution involves pivoting the data, which is known as creating a pivot table. The process involves:

1. Prepare the 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

2. Interpret the Query:

   • The query uses conditional aggregation with the CASE expression to assign designations to the corresponding type columns (202, 234, 239).
   • The MAX function aggregates the designations within each group of ID and Email.
   • The GROUP BY clause combines the results based on ID and Email to create the pivot table format.

Note:

It's important to note that the query assumes you know the distinct Type values in advance. In SQL, column definitions must be fixed during query preparation. If you have a varying set of Type values, you will need to use a dynamic query or stored procedure to generate the appropriate query at runtime.

The above is the detailed content of How to Transform a MySQL Result Set into a Pivot Table Using Conditional Aggregation?. For more information, please follow other related articles on the PHP Chinese website!