How to Perform Row to Column Transformation (Pivot) in MySQL with Dynamic Data?

Row to Column Transformation in MySQL: Pivot Table for Dynamic Data

Pivot tables are a convenient way to transform data from a row-oriented format to a column-oriented format, allowing for easier analysis and reporting. In MySQL, this transformation can be achieved through a specialized technique.

Consider the following example data:

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

To pivot this data, we utilize 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 employs the CASE expression to dynamically create columns for each distinct Type value. The MAX function is used to aggregate the Degignation values for each ID and Email combination.

It's important to note that for this query to work effectively, you must know all the possible Type values beforehand. MySQL does not allow dynamic addition of columns based on data discovered at runtime; column definitions must be static during query preparation.

This technique allows you to transform row-oriented data into a column-oriented format, facilitating analysis and reporting tasks by providing a more intuitive data presentation.

The above is the detailed content of How to Perform Row to Column Transformation (Pivot) in MySQL with Dynamic Data?. For more information, please follow other related articles on the PHP Chinese website!