CODEWITHSUNDEEP
postgresqlpivotpivot-tablepostgres-crosstab
premshiva
premshiva

Reputation: 21

PIVOT/crosstab: I wan to return dynamic columns

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
);

Upvotes: 2

Views: 5308

Answers (2)

L. Rodgers
L. Rodgers

Reputation: 207

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

Upvotes: 1

Edouard
Edouard

Reputation: 7065

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

Upvotes: 2

Related Questions