How to Simulate Pivot Tables in MySQL Using UNION ALL and Aggregation?

Simulating a pivot table in MySQL: using UNION ALL and aggregate functions

Pivot tables play a key role in data analysis and report generation, converting columnar data into row-based data to achieve more compact and insightful visualizations. Although MySQL does not directly provide UNPIVOT and PIVOT functions, we can cleverly combine UNION ALL and aggregate functions to achieve similar functions.

Data Unpivoting

The first step in a pivot table is to expand the data, or convert it into a more flexible format. This involves creating multiple rows for each column of data, where each row represents a column and its corresponding value. We can use the UNION ALL operator to achieve this:

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;

This query will generate the following 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

Expand Pivoting of Data

Once the data is expanded, we can pivot it using aggregate functions and CASE statements to create the desired report format:

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;

The above query will produce the following results:

descrip	jan	feb
col1	A	C
col2	B	A
col3	0	G
col4	B	E

This result matches the format of the required report. Through these technologies, we can effectively implement pivot table functions in MySQL and easily convert column-based data into row-based reports, allowing for more efficient analysis and reporting.

The above is the detailed content of How to Simulate Pivot Tables in MySQL Using UNION ALL and Aggregation?. For more information, please follow other related articles on the PHP Chinese website!