How to pivot a table - Postgresql

Question

I have table

Team profit spend net_profit
A     3     2     1
B     6     5     1

How to have result like this

Team Category    Total
A    profit      3
A    spend       2
A    net_profit  1
B
B
B

I have research and it seem there are many way like cross tab or case when but I haven't found a solution, Is there the simplest?

Answer 1

You can use values() and a lateral join to unpivot your dataset:

select t.team, x.* 
from mytable t
cross join lateral (values 
    ('profit',     profit), 
    ('spend',      spend), 
    ('net_profit', net_profit)
) as x(category, total)