How to Pivot Columns into Rows in MySQL for Reporting?

MySQL Pivot: Convert columns to rows

In relational databases such as MySQL, data often needs to be transformed for reporting purposes. One such transformation involves converting columns into rows, a process called "pivoting." This technique allows for more flexible and concise data representation.

Question:

Consider the following MySQL table:

<code class="language-sql">CREATE TABLE mytable (
  id INT, 
  month VARCHAR(3), 
  col1 VARCHAR(1), 
  col2 VARCHAR(1), 
  col3 VARCHAR(1), 
  col4 VARCHAR(1)
);

INSERT INTO mytable (id, month, col1, col2, col3, col4) VALUES
(101, 'Jan', 'A', 'B', NULL, 'B'),
(102, 'Feb', 'C', 'A', 'G', 'E');</code>

The goal is to create a report that displays the values ​​from col1 to col4 as rows and the months Jan and Feb as columns.

Solution:

MySQL does not provide built-in functions for unpivoting or pivoting data. However, we can simulate these operations using UNION ALL and aggregates with CASE expressions.

1. Anti-perspective:

To unpivot the data, we create a new subquery that uses UNION ALL to combine rows from all columns into a single column:

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

2. Perspective:

Next, we wrap the unpivot query in a subquery and use aggregates and CASE statements to transform the data into the required format:

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

Result:

Descrip	Jan	Feb
col1	A	C
col2	B	A
col3	NULL	G
col4	B	E

Note: Change ELSE 0 END to ELSE NULL END to make the result more consistent with the database specifications and avoid unnecessary 0 values.

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