Using crosstab with dynamic number of columns

Question

I have a table with data:

create Table ProductSales
(    
    Productname varchar(50),
    Year int,
    Sales int
);
 
Insert into ProductSales 
values ('A', 2017, 100),
       ('A', 2018, 150),
       ('A', 2019, 300),
       ('A', 2020, 500),
       ('A', 2021, 450),
       ('A', 2022, 675),
       ('B', 2017, null),
       ('B', 2019, 1120),
       ('B', 2020, 750),
       ('B', 2021, 1500),
       ('B', 2022, 1980);

Productname	Year	Sales
A	2017	100
A	2018	150
A	2019	300
A	2020	500
A	2021	450
A	2022	675
B	2017	null
B	2019	1120
B	2020	750
B	2021	1500
B	2022	1980

And I want to get the result like this:

Productname	2017	2018	2019	2020	2021	2022
A	100	150	300	500	450	675
B	null	null	1120	750	1500	1980

Is it possible to make it with postgresql? I tried to use crosstab() but failed.

Besides, it's possible to have separate table with need years, e.g.:

|Year|
|----|
|2017|
|2018|
|2019|
|2020|

Does it change something?

Answer 1

You can't have a dynamic number of columns in a single SQL query.

If you use two, the first one can determine your target structure, the second one use it. You can have dynamic SQL construct and

1. print the query for you to use
2. prepare a statement for you to only call an execute on
3. define a function for you to call
4. execute it, writing output to a table that you can read from in 2nd step.

The list isn't exhaustive. None of this is pretty, all is error-prone. You can keep pushing how "dynamic" it gets until you run out of patience (or prescription meds): demo extending Tushar's

CREATE PROCEDURE first_step_procedure() LANGUAGE PLPGSQL AS $procedure$
BEGIN
  IF EXISTS (SELECT true 
             FROM pg_prepared_statements 
             WHERE name='second_step_statement') THEN
      DEALLOCATE second_step_statement;
  END IF;
  EXECUTE format(   $prep$
                    PREPARE second_step_statement AS
                    SELECT *
                    FROM crosstab(
                        'SELECT Productname, Year, Sales
                        FROM ProductSales
                        ORDER BY 1, 2'
                      , 'SELECT DISTINCT Year FROM ProductSales ORDER BY 1'
                    ) AS ct ("Productname" text, %1$s) 
                    $prep$
                  , (SELECT string_agg(format('%I int',Year),', ')
                     FROM (SELECT DISTINCT Year 
                           FROM ProductSales 
                           ORDER BY 1) AS a ) );
END $procedure$;

And then, each time you want to use it, you need to repeat your two steps:

call first_step_procedure();--determines the structure to redefine your statement
execute second_step_statement;

create table keep_output_in_a_table as execute second_step_statement;

I went with prepared statements because they are at least session-specific - other users can share the first_step_procedure() but their second_step_statement generated by it won't be interfering.

---

If you want to maintain a table(view) based on that ProductSales, you can push this further with a trigger that keeps redefining the dependent object: demo

create Table ProductSales (
    Productname varchar(50),
    Year int,
    Sales int );
CREATE FUNCTION ProductSalesYearly_maintainer_function() RETURNS TRIGGER
  language plpgsql as $procedure$
BEGIN
  EXECUTE format(   $prep$
                    drop table if exists ProductSalesYearly;
                    CREATE TABLE ProductSalesYearly AS
                    SELECT *
                    FROM crosstab(
                        'SELECT Productname, Year, Sales
                        FROM ProductSales
                        ORDER BY 1, 2'
                      , 'SELECT DISTINCT Year FROM ProductSales ORDER BY 1'
                    ) AS ct ("Productname" text, %1$s) 
                    $prep$
                  , (SELECT string_agg(format('%I int',Year),', ')
                     FROM (SELECT DISTINCT Year 
                           FROM ProductSales 
                           ORDER BY 1) AS a ) );
   RETURN NULL;
END $procedure$;

CREATE TRIGGER ProductSalesYearly_maintainer_trigger
    AFTER INSERT OR UPDATE OR DELETE ON ProductSales
    FOR EACH STATEMENT
    EXECUTE FUNCTION ProductSalesYearly_maintainer_function();

Keep in mind that static references to ProductSalesYearly that's being constantly redefined, keep breaking - even though the name remains the same, it's a new object. It also keeps firing on all traffic on ProductSales. You could mitigate that to an extent by constructing "migrations", altering the table adding/removing columns each time, instead of dropping and reconstructing.

Answer 2

By using your data in DBFIDDLE

The working query using crosstab can be written as :

SELECT *
FROM crosstab(
  'SELECT Productname, Year, Sales
   FROM ProductSales
   ORDER BY 1, 2',
   'SELECT DISTINCT Year FROM ProductSales ORDER BY 1'
) AS ct ("Productname" varchar(50), "2017" int, "2018" int, "2019" int, "2020" int, "2021" int, "2022" int);

This gives me expected output :

Productname	2017	2018	2019	2020	2021	2022
A	100	150	300	500	450	675
B	null	null	1120	750	1500	1980