How to Unpivot MySQL Columns into Rows Using UNION ALL or CROSS JOIN?

Restructuring MySQL Columns into Rows

Data transformation is a common task in database management. In MySQL, converting column data into rows—a process known as unpivoting—is frequently necessary for improved data analysis or presentation. While MySQL lacks a dedicated unpivot function, this transformation can be efficiently achieved using either a UNION ALL query or a CROSS JOIN.

The UNION ALL Method

One effective strategy employs the UNION ALL operator:

SELECT id, 'a' AS col, a AS value
FROM yourtable
UNION ALL
SELECT id, 'b' AS col, b AS value
FROM yourtable
UNION ALL
SELECT id, 'c' AS col, c AS value
FROM yourtable;

This query retrieves the id column alongside the column name (col) and its corresponding value (value). The outcome is a restructured dataset where each row represents a single column-value pair.

The CROSS JOIN Approach

Alternatively, a CROSS JOIN can be utilized:

SELECT t.id,
       c.col,
       CASE c.col
           WHEN 'a' THEN a
           WHEN 'b' THEN b
           WHEN 'c' THEN c
       END AS data
FROM yourtable t
CROSS JOIN
(
  SELECT 'a' AS col
  UNION ALL SELECT 'b'
  UNION ALL SELECT 'c'
) c;

Here, yourtable (aliased as t) is joined with a subquery that generates a list of column names (col). The CASE statement dynamically selects the appropriate value based on the column name. The result, much like the UNION ALL method, is a flattened dataset with each row containing the id, column name, and its associated value.

Both the UNION ALL and CROSS JOIN techniques provide robust solutions for unpivoting columns in MySQL, enabling the conversion of columnar data into rows for subsequent processing or visualization.

The above is the detailed content of How to Unpivot MySQL Columns into Rows Using UNION ALL or CROSS JOIN?. For more information, please follow other related articles on the PHP Chinese website!