Generate dynamic columns for CROSSTAB in postgresql?

Question

I have this table in postgres

CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');

I want to generate a dynamic crosstab query using this table. Till now I have created the static query by following the example on the official postgres documentation page.

select * from crosstab
('select rowid, attribute, value from ct order by 1,2')
as final_result(rowid text, att1 text, att2 text, att3 text, att4 text)

Now I want this part to be dynamic

as final_result(rowid text, att1 text, att2 text, att3 text, att4 text)

I tried few things such as

Creating a query which generate the column name with their types and passing that query in as final_result(query), but it doesn't work as here,

SELECT 'rowid text, '
       || string_agg(Distinct attribute, ' text, ') as name
FROM   ct;

select * from crosstab
('select rowid, attribute, value from ct order by 1,2')
as final_result(SELECT 'rowid text, '
       || string_agg(Distinct attribute, ' text, ') as name
FROM   ct;)

OR

select * from crosstab
('select rowid, attribute, value from ct order by 1,2', 
SELECT 'rowid text, '
       || string_agg(Distinct attribute, ' text, ')) as name
FROM   ct;)

Both of these queries doesn't work.

I searched stackoverflow found this link, but it also doesn't have a proper acceptable answer here,

Dynamically generate columns for crosstab in PostgreSQL

Any idea how this can be done.

That's not possible. The number and datatypes of the columns of a query must be known **before** the query starts. — , May 27 '20 at 08:00
SELECT 'rowid text, ' || string_agg(Distinct attribute, ' text, ')) as name FROM ct; — John, May 27 '20 at 08:02

Generate dynamic columns for CROSSTAB in postgresql?

0 Answers0