How to Pivot Data in MySQL: Transforming Columns into Rows?

MySQL Pivot: Convert columns to rows

In MySQL, a pivot table needs to be generated by converting columns into rows for better presentation of data. For example, consider a table that contains multiple columns (for example, col1, col2) representing data for different months (for example, January, February).

Requirements: Create a report that presents data in a pivot table format, where each row represents a column in the original table and each column represents a different month.

Data expansion:

MySQL lacks built-in functions for unfolding data (converting columns to rows). However, this can be achieved with a UNION ALL query:

<code class="language-sql">SELECT id, month, col1 AS value, 'col1' AS descrip
FROM yourtable
UNION ALL
SELECT id, month, col2 AS value, 'col2' AS descrip
FROM yourtable
UNION ALL
SELECT id, month, col3 AS value, 'col3' AS descrip
FROM yourtable
UNION ALL
SELECT id, month, col4 AS value, 'col4' AS descrip
FROM yourtable</code>

Result:

This query converts the data into the following format:

ID	MONTH	VALUE	DESCRIP
101	Jan	A	col1
101	Jan	B	col2
101	Jan	NULL	col3
101	Jan	B	col4
102	Feb	C	col1
102	Feb	A	col2
102	Feb	G	col3
102	Feb	E	col4

Pivot:

Once the data is expanded, it can be converted into the desired pivot table format using aggregate functions. The following query uses a CASE statement to group the data by each column (descrip) and display the values ​​for January and February:

<code class="language-sql">SELECT descrip,
MAX(CASE WHEN month = 'Jan' THEN value ELSE 0 END) AS Jan,
MAX(CASE WHEN month = 'Feb' THEN value ELSE 0 END) AS Feb
FROM
(
SELECT id, month, col1 AS value, 'col1' AS descrip
FROM yourtable
UNION ALL
SELECT id, month, col2 AS value, 'col2' AS descrip
FROM yourtable
UNION ALL
SELECT id, month, col3 AS value, 'col3' AS descrip
FROM yourtable
UNION ALL
SELECT id, month, col4 AS value, 'col4' AS descrip
FROM yourtable
) src
GROUP BY descrip</code>

Result:

This query generates the required pivot table format:

DESCRIP	JAN	FEB
col1	A	C
col2	B	A
col3	0	G
col4	B	E

The above is the detailed content of How to Pivot Data in MySQL: Transforming Columns into Rows?. For more information, please follow other related articles on the PHP Chinese website!