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

Actual table structure

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

enter image description here

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?

  • Not possible. One fundamental restriction of the SQL language is, that all columns must be known to the database engine while parsing the statement. It's not possible to define column names in the output based on the data that is obtained while the statement is running. Nov 9 '21 at 7:33
  • @a_horse_with_no_name I do have the column names in another table could we use that some how ?
    – Maverick
    Nov 9 '21 at 8:52

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.