Postgres pivot columns to rows

Question

How can I convert table of the following 5 columns structure:

Id, name, col1, col2, col2
1   aaa   10    20    30
2   bbb   100   200   300

to the following structure where Col1, Col2 and Col3 columns are now shown as strings in new columns Colx.

Id, name, Colx, Value
1   aaa   Col1  10
1   aaa   Col2  20
1   aaa   Col3  30
2   bbb   Col1  100
2   bbb   Col2  200
2   bbb   Col3  300

Thanks! Avi

Have you tried anything? Do you have any idea that you aren't able to put in a query? You shouldn't just *ask for a solution*... — Eggplant, Nov 10 '15 at 15:44
I was able to do it with Union as follows select id, name, 'Col1' as Colx, Col1 as Value union select id, name, 'Col2' as Colx, Col2 as Value, but I will be happy to get a more elegant solution — user1409708, Nov 10 '15 at 15:46
That's right, an `UNION ALL` actually would solve the problem. — Eggplant, Nov 10 '15 at 15:47

score 0 · Answer 1 · answered Nov 10 '15 at 16:07

You can use a subquery with UNION statement

select nombre, colx, val from (
    select nombre, 'col1' as colx, col1 as val from test
    UNION
    select nombre, 'col2' as colx, col2 as val from test
    UNION
    select nombre, 'col3' as colx, col3 as val from test
) as query
order by val

Postgres pivot columns to rows

1 Answers1