Using crosstab with dynamic number of columns – Postgresql

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.