How to Unpivot Columns into Rows in MySQL?

Tricks to convert columns to rows in MySQL

When working with column-organized data in MySQL, you may need to convert it to a format that presents the data in rows. This process is called unpivoting.

Question:

Consider the following form:

<code>ID | a  | b  | c 
1  | a1 | b1 | c1
2  | a2 | b2 | c2</code>

The goal is to convert columns into rows to get a table with the following structure:

<code>ID | 值 | 列名
1  | a1 | a
1  | b1 | b
1  | c1 | c
2  | a2 | a
2  | b2 | b
2  | c2 | c</code>

Solution:

Unlike some other databases, MySQL does not have a built-in unwinding function. To get the results you want, you can use the UNION ALL query to combine separate queries that selectively convert each column into rows:

<code class="language-sql">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;</code>

This query generates three sets of data: one for each column. Each group contains the ID, column name, and corresponding value.

Use CROSS JOIN:

Another way is to use CROSS JOIN:

<code class="language-sql">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;</code>

In this case, CROSS JOIN creates a Cartesian product between the rows of yourtable and the rows of the subquery that generates the column names. The CASE statement maps each column name to its corresponding column value.

Both methods effectively unwind the data, giving you the row-based structure you want.

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