Search code examples
sqlpostgresqlunpivot

Postgres transpose a row

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.

Solution

  • 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.