PIVOT/crosstab: I wan to return dynamic columns

Question

I just want to return a dynamic columns out of cross tab, column names based out of query result. Header is lookup from different table like Types.

any help appreciated. so far i could able to get hard coded columns.

for example

Result 1:

header	B header	C header	D header
First	111	12	1
Second	33	34	0

Result 2:

header	X header	Y header	D header
First	11	123	11
Second	313	343	12

SELECT * FROM crosstab(
$$
    SELECT property, name, count(prm_id)
    FROM vw_ex_insp_sum
    WHERE prm_id = 1
    AND date_t BETWEEN '2021/01/01' AND '2021/1/31'
    GROUP BY property, name
    HAVING count(prm_id) >0
$$,
$$
    SELECT name FROM lookup WHERE l_type=12
    ORDER BY vit 
$$
) AS final_result (
    Name2 varchar,
    "Annual (365)" bigint, "Follow-Up (30)" bigint, "3" bigint, "4" bigint, "5" bigint ,
    name_0 bigint, name_1 bigint, "8" bigint, "Indicator (Post-Event)" bigint, "10" bigint ,
    "11" bigint, "12" bigint, "13" bigint, "14" bigint, "15" bigint ,
    "16" bigint
);

Not possible. A fundamental restriction of the SQL language is, that the number, names and types of the columns of a query must be known before the query starts running. — , Jan 27 '22 at 06:43

score 2 · Answer 1 · answered Jan 27 '22 at 10:12

2

Here below are two examples for implementing a full dynamic pivot-table solution instead of using crosstab :

https://stackoverflow.com/a/70466824/8060017

https://stackoverflow.com/a/70695023/8060017

answered Jan 27 '22 at 10:12

Edouard

6,577
1
9
20

Thanks, im trying to get in. Have you used CROSSTABVIEW ? i need a help to get this called from ruby. is this still possible? – premshiva Feb 01 '22 at 02:19
https://wiki.postgresql.org/wiki/Crosstabview – premshiva Feb 01 '22 at 02:20

score 1 · Answer 2 · answered Sep 22 '22 at 12:06

This question has been asked many times, & there are decent (even dynamic) solutions. While CROSSTAB() is available in recent versions of Postgres, not everyone has sufficient user privileges to install the prerequisite extension.

One such solution involves a temp type (temp table) created by an anonymous function & JSON expansion of the resultant type.

Stack Overflow: How to pivot or crosstab in postgresql without writing a function?

See also: DB FIDDLE (UK): https://dbfiddle.uk/Sn7iO4zL

PIVOT/crosstab: I wan to return dynamic columns

2 Answers2