How to “flatten” or “collapse” a 1D mysql table into 2D?

Question

I have a table like this:

id  -  name  -  value
1   -  shoes - 21
1   -  gloves - 5
2   -  shoes - 23
2   -  gloves - 3

I want it to be converted to a table like this:

id  -  shoes  - gloves
1   -  21     - 5
2   -  23     - 3

Is it possible to do it with one query in Mysql? I also want the name of columns to be generated from the name column in the 1st table.

Check: https://stackoverflow.com/questions/7674786/mysql-pivot-table — Madhur Bhaiya, Oct 09 '18 at 12:42
Also, consider handling such display related issues in your application code. — Madhur Bhaiya, Oct 09 '18 at 12:43
You can look at Dynamic SQL based solutions here: http://webdevzoom.com/dynamic-pivot-table-using-mysql/ — Madhur Bhaiya, Oct 09 '18 at 12:44

score 0 · Answer 1 · answered Oct 09 '18 at 12:55

0

You may try to use join like this:

SELECT main.id, s.name AS shoes, g.name AS gloves
FROM tbl AS main
LEFT JOIN tbl s ON main.id = s.id
LEFT JOIN tbl g ON main.id = g.id

answered Oct 09 '18 at 12:55

cn007b

1 Answers1