Skip to content
Advertisement

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.

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.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement