How to Unpivot and Pivot Data in MySQL to Transform Columns into Rows?

MySQL data pivot and pivot: convert columns into rows

Suppose you have a table with multiple columns (col1, col2, col3, etc.) and want to convert it to a pivot table format, presenting the data in row units rather than columns.

To achieve this in MySQL, first use the UNION ALL query to pivot the data. This process creates multiple rows from the original column values:

<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>

UNION ALL Query results:

ID	MONTH	VALUE	DESCRIP
101	Jan	A	col1
102	feb	C	col1
101	Jan	B	col2
102	feb	A	col2
101	Jan	(null)	col3
102	feb	G	col3
101	Jan	B	col4
102	feb	E	col4

Next, wrap the UNION ALL query in a subquery to pivot the data. Use aggregate functions GROUP BY and CASE statements to convert the structure of the unpivoted perspective into the desired perspective format:

<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
) AS src
GROUP BY descrip;</code>

Results of pivot query:

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

This process effectively converts the table structure from columns to rows, allowing you to present your data in a more concise and clear way.

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