MySQL - put rows as columns and columns as rows?

Question

How can I transpose a row to a column in MySQL?

I've read some questions on this topic on StackOverflow, but I still have no idea how I can achieve the result that I want. I've also tried some code without success.

The following sample will hopefully make my desired result clear. I created a view named "summary_kcal" grouped by user-ID. It is composed as below:

+---------+------+------+------+------+------+------+------+
| userid  |  LU  |  MA  |  ME  |  GIO |  VE  |  SA  | DO   | 
+---------+------+------+------+------+------+------+------+
| 1       | 1000 | 2000 | 1000 | 1500 | 2000 | 1000 | 1500 |
+---------+------+------+------+------+------+------+------+

I want to convert it to:

+---------+---------+
| userid  | 1       |
+---------+---------+
| LU      | 1000    | 
+---------+---------+
| MA      | 2000    | 
+---------+---------+
| ME      | 1000    |
+---------+---------+
| GIO     | 1500    |
+---------+---------+
| VE      | 2000    |
+---------+---------+
| SA      | 1000    |
+---------+---------+
| DO      | 1500    |
+---------+---------+

Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Uueerdo, Dec 17 '18 at 19:02
@uueerdo looks more like unpivot, but really, so vague, who knows? — Strawberry, Dec 17 '18 at 19:29
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Dec 17 '18 at 19:37
@Strawberry Ah, yeah, I see that now. I must have skimmed too fast, as I thought the first block was the desired format. — Uueerdo, Dec 17 '18 at 19:47

Uueerdo · Answer 1 · 2018-12-17T20:18:17.130

You're usually better off doing this kind of transform client side, but if you really need to do it in the database....

SELECT "LU" AS userid, `LU` AS `1` WHERE userid = 1
UNION
SELECT "MA" AS userid, `MA` AS `1` WHERE userid = 1
UNION
SELECT "ME" AS userid, `ME` AS `1` WHERE userid = 1
UNION
SELECT "GIO" AS userid, `GIO` AS `1` WHERE userid = 1
UNION
SELECT "VE" AS userid, `VE` AS `1` WHERE userid = 1
UNION
SELECT "SA" AS userid, `SA` AS `1` WHERE userid = 1
UNION
SELECT "DO" AS userid, `DO` AS `1` WHERE userid = 1

Note: You only actually need the aliases on the first query, just included for clarity and consistency.

In the event you want multiple columns, one for each user row, this gets unmanageable rather quickly as each XX AS # WHERE userid = # ends up needing to be a separate subquery instead (or a large unwieldy GROUP CONCAT query with conditional aggregation).

MySQL - put rows as columns and columns as rows?

1 Answers1