I want to pivot a table based on a field which can contain "dynamic" values (not always known beforehand).
I can make it work by hard coding the values (which is undesirable):
SELECT *
FROM my_table
pivot(SUM(amount) FOR type_id IN (1,2,3,4,5,20,50,83,141,...));
But I can't make it work using a query to provide the values dynamically:
SELECT *
FROM my_table
pivot(SUM(amount) FOR type_id IN (SELECT id FROM types));
---
090150 (22000): Single-row subquery returns more than one row.
SELECT *
FROM my_table
pivot(SUM(amount) FOR type_id IN (SELECT ARRAY_AGG(id) FROM types));
---
001038 (22023): SQL compilation error:
Can not convert parameter 'my_table.type_id' of type [NUMBER(38,0)] into expected type [ARRAY]
Is there a way to accomplish this?
Snowflake now supports dynamic pivot using the ANY keyword in the PIVOT IN list or a Subquery e.g.
select *
from performance_reviews
pivot (avg(rating) for skill in (ANY))
or
select *
from performance_reviews
pivot (avg(rating) for skill in (select skill from performance_reviews where rating > 4));