PostgreSQL table transformation

Question

I have the table in below format :

id	year	column1	column2	column3
1	2000	10	20	30
1	2001	100	200	300

I want the transformed version to be like -

id	Field	value_2000	value_2001
1	column1	10	100
1	column2	20	200
1	column3	30	300

Appreciate any input!

I was able to take the column names in rows using unnest function. But facing some issues with taking the corresponding values.

Answer 1

You would typically unpivot the rows to columns in a lateral join, then pivot to the target dataset with conditional aggregation.

select t.id, x.col,
    max(x.val) filter(where t.year = 2000) val_2000,
    max(x.val) filter(where t.year = 2001) val_2001
from mytable t
cross join lateral ( values
    ( 'column1', column1 ),
    ( 'column2', column2 ),
    ( 'column3', column3 )
) x(col, val)
group by t.id, x.col
order by t.id, x.col

Note that this works for a fixed list of columns and of years - otherwise you need dynamic SQL (ie generate the query text in SQL, then execute it), which is a rather different beast.