CODEWITHSUNDEEP
mysqlsqlpivot
Vlad Matao
Vlad Matao

Reputation: 15

Pivoting table sql

I have this table:

|............id.............|............a............. |.............b............|
|...........123qwe....      |...........0.............  |.............13...........|

and I need to pivot it like this:

|.........id................|........indicator....      |.............value........ |
|...........123qwe....      |...........a.............  |.............0.............|
|...........123qwe....      |...........b.............  |.............13........... |

There are more thatn 100 columns with indicators as headers (a,b,c,d,e,f,...) so a sort of loop would be needed.

Upvotes: 0

Views: 78

Answers (2)

Saul Meng
Saul Meng

Reputation: 61

Do it like this：

SELECT id, 'a' AS indicator, a AS value
FROM test
UNION
SELECT id, 'b' AS indicator, b AS value
FROM test;

and if you want loop, you'd better write it in the program...

Upvotes: 0

symcbean
symcbean

Reputation: 48357

 SELECT id, 'a', SUM(a)
 FROM yourtable
 GROUP BY id
 UNION 
 SELECT id, 'b', SUM(b)
 FROM yourtable
 GROUP BY id
 UNION
 ...

As usual, the right answer is to normalize you schema.

Upvotes: 1

Related Questions