How to pivot on dynamic values in Snowflake

Question

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?

Answer 1

I don't think it's possible in native SQL, but I wrote an article and published some code showing how my team does this by generating the query from Python.

You can call the Python script directly, passing arguments similar to the options Excel gives you for pivot tables:

python generate_pivot_query.py                  \
    --dbtype snowflake --database mydb          \
    --host myhost.url --port 5432               \
    --user me --password myp4ssw0rd             \
    --base-columns customer_id                  \
    --pivot-columns category                    \
    --exclude-columns order_id                  \
    --aggfunction-mappings amount=sum           \
    myschema orders

Or, if you're Airflow, you can use a CreatePivotTableOperator to create tasks directly.