How to transpose column to row and duplicate the original column header?

I have a table for example as below, the alphabet is header and the numbers are its records

a   b   c   d   e   f
---------------------
3   4   5   6   2   3
5   3   2   9   8   7

I want to transpose the table to be like below:

col1 col2
---------
a   3
b   4
c   5
d   6
e   2
f   3
a   5
b   3
c   2
d   9
e   8
f   7

This is not just transposing from col to row, but I also need to copy the alphabet header to as many as the original row (If I have 2 original rows then I need to copy the header twice, if I have 3 then I have to copy 3 times). I’m new in postgresql, how to achieve this?

Answer

Use a lateral join:

select v.*
from t cross join lateral
     (values ('a', a), ('b', b), ('c', c), ('d', d), ('e', e), ('f', f)
     ) v(col, val);

Compared to the union all approach, this only scans the table once. This can be a bit performance win if your “table” is really a more complex query.

Advertisement

Answer