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.
You can use a lateral cross join:
select l.*
from the_table t
cross join lateral (
values ('a', a), ('b', b), ('c', c) ('d', d)
) as l(column_name, value);
Another option is to convert the row to a JSON value, then use jsonb_each_text() to extract the key value pairs. This is a bit more dynamic, but the downside is that you need to cast the values back to an integer:
select l.column_name,
l.value::int as value
from the_table t
cross join lateral jsonb_each_text(to_jsonb(t)) as l(column_name, value);
If you have a primary (or unique) key column in the table, you might want to add that to the SELECT list (select t.id, l.* ...), so you know from which row each value came from.
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.
