[SOLVED] Pivot on multiple columns in snowflake

Pivot on multiple columns in snowflake

I have following table in snowflake table

ID     | NAME | QTY | LAYER
5026767  AB    25    1
5026767  XY    25    3
5026767  QE    50    2
5027016  TI    100   1
5027038  PP    30    1
5027038  CC    30    3
5027038  DD    40    2

What I want to aggregate NAME and QTY pivoted on LAYER. Such like following the output

ID      | '1' | '2' | '3' | NAME_1 | NAME_2 | NAME_3
5026767  25    25    50    AB       XY       QE  
5027016  100   Null  Null  TI       Null     Null
5027038  30    30    40    PP       CC       DD

I have tried with pivot but there are only aggregation available related to number not for char. Is there something I am missing?

Solution

if you want just one name, then min/max are the way to go..

with cte_data(id,name,qty,layer) as (
    select * from values
    ('5026767','AB', 25 ,   1),
    ('5026767','XY', 25 ,   3),
    ('5026767','QE', 50 ,   2),
    ('5027016','TI', 100,   1),
    ('5027038','PP', 30 ,   1),
    ('5027038','CC', 30 ,   3),
    ('5027038','DD', 40 ,   2)
)
select id
    ,sum(iff(layer=1,qty,null)) as "1"
    ,sum(iff(layer=2,qty,null)) as "2"
    ,sum(iff(layer=3,qty,null)) as "3"
    ,max(iff(layer=1,name,null)) as name_1
    ,max(iff(layer=2,name,null)) as name_2
    ,max(iff(layer=3,name,null)) as name_3
from cte_data