How to pivot rows into columns in MySQL?

2 years ago
Liam
1 minute

In MySQL, the UNION operator can be used to pivot columns into rows.

For example, suppose there is the following table:

+----+-------+
| id | value |
+----+-------+
|  1 |   10  |
|  2 |   20  |
|  3 |   30  |
+----+-------+

To convert the ‘value’ column in the table to rows, you can use the following query:

SELECT 'value' AS type, value FROM table_name
UNION
SELECT 'id' AS type, CAST(id AS CHAR) AS value FROM table_name;

The search results are as follows:

+-------+-------+
| type  | value |
+-------+-------+
| value |   10  |
| value |   20  |
| value |   30  |
|   id  |    1  |
|   id  |    2  |
|   id  |    3  |
+-------+-------+

The first SELECT statement in the above query selects the value column for rows with the type ‘value’, while the second SELECT statement selects the id column for rows with the type ‘id’. Finally, the UNION is used to combine the two result sets into one result set.

bannerAds