dynamicpivotsnowflake-cloud-data-platform

How to pivot on dynamic values in Snowflake

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?

Solution

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