My concern here is if we have a dynamic set of column names that need to be pivoted, how can we achieve it. I have read that PIVOT supports subquery but never worked for me.
PIVOT syntax-
SELECT * FROM
(
SELECT column1, column2
FROM tables
WHERE conditions
)
PIVOT
(
aggregate_function(column2)
FOR column2
IN ( expr1, expr2, ... expr_n) | subquery
)
ORDER BY expression [ ASC | DESC ];
In the IN clause it is mentioned that we can use subquery but it didn't worked. It will great if anyone can help/provide any example which is using subquery instead of static values.
Thanks,
Shilpi