I have a table which looks like the following :-
a b c d e 29 14 11 16 8
I want to transpose it to the following form, in Postgres:
a 29 b 14 c 11 d 16 e 8
I know I’m supposed to use the pivot
operation to do this, but I can’t figure out the right syntax to get this done.
Any help to get this done will be appreciated.
Advertisement
Answer
That would simply be:
select 'a' as column_name, a as value from mytable union all select 'b' as column_name, b as value from mytable union all select 'c' as column_name, c as value from mytable union all select 'd' as column_name, d as value from mytable union all select 'e' as column_name, e as value from mytable
but with multiple rows in the table you don’t see anymore, which a
is related to which b
, etc. So while this matches your request, you may want to think over, whether this is really what you want to do.