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.
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.