Postgres Crosstab on double columns with unknown value

Question

So i have a table like this in my Postgres v.10 DB

CREATE TABLE t1(id integer primary key, ref integer,v_id integer,total numeric, year varchar, total_lastyear numeric,lastyear varchar ) ;
    INSERT INTO t1 VALUES
    
    (1,  2077,15,10000,2020,9000,2019),
    (2,  2000,13,190000,2020,189000,2019),
    (3,  2065,11,10000,2020,10000,2019),
    (4,  1999,14,2300,2020,9000,2019);

select * from t1 =

id  ref    v_id total   year    total_lastyear  lastyear
1   2077    15  10000   2020       9000          2019
2   2000    13  190000  2020       189000        2019
3   2065    11  10000   2020       10000         2019
4   1999    14  2300    2020       9000          2019

Now i want to Pivot this table so that i have 2020 and 2019 as columns with the total amounts as values.

My Problems:

I don't know how two pivot two columns in the same query, is that even possibly or do you have to make two steps?
The years 2020 and 2019 are dynamic and can change from one day to another. The year inside the column is the same on every row.
So basicly i need to save the years inside lastyear and year in some variable and pass it to the Crosstab query.

This far i made it myself but i only managed to pivot one year and the 2019 and 2020 years is hardcoded. Demo

score 1 · Accepted Answer · answered Feb 08 '22 at 14:50

You can pivot one at a time with WITH.

WITH xd1 AS (
        SELECT * FROM crosstab('SELECT ref,v_id,year,total FROM t1 ORDER BY 1,3',
 'SELECT DISTINCT year FROM t1 ORDER BY 1') AS ct1(ref int,v_id int,"2020" int)
     ), xd2 AS (
        SELECT * FROM crosstab('SELECT ref,v_id,lastyear,total_lastyear FROM t1 ORDER BY 1,3',
 'SELECT DISTINCT lastyear FROM t1 ORDER BY 1') AS ct2(ref int,v_id int,"2019" int)
     )
SELECT xd1.ref,xd1.v_id,xd1."2020",xxx."2019"
FROM xd1
LEFT JOIN xd2 AS xxx ON xxx.ref = xd1.ref AND xxx.v_id = xd1.v_id;

This doesn't prevent from last_year and year colliding. You still have to know the years query will return as you have to define record as it is returned by crosstab.

You could wrap it in an EXECUTE format() to make it more dynamic and deal with some stringology. This issue was mentioned here.

Postgres Crosstab on double columns with unknown value

1 Answers1