How to set the column names dynamically in crosstab?

Question

I have a table with key and value and I have saved the values as shown below

when I use the below query

select * from crosstab (
    'select rowname,colname,max(cellval) from table_to_pivot group by 1,2 order by 1,2',
    'select distinct colname from table_to_pivot order by 1'
  ) as newtable (rowname varchar,_col1 integer,_col2 integer,_col3 integer);

I am getting my desired output as shown below

but I don't want to define the column names [_col1 integer,_col2 integer,_col3 integer] in my query, I want it to be dynamic. Is there a way to achieve this?

Answer 1

An SQL engine must determine the names and types of all output columns of a query before executing it.

If the output columns are not known in advance and thus cannot be hardcoded into the query, then the problem must be solved in two separate steps:

1. generate the crosstab query with another query
2. execute the crosstab query

Or the problem must be changed to accept results embedded in a JSON column fed with json_object_agg (as illustrated in Static and dynamic pivots).

There are multiple answers about this on stackoverflow exploring various workarounds. Dynamic alternative to pivot with CASE and GROUP BY provides quite a bit to read and pointers to other Q/As.