0

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
);
Edouard
  • 4,508
  • 1
  • 7
  • 17
premshiva
  • 1

1 Answers1

0

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

Edouard
  • 4,508
  • 1
  • 7
  • 17