Keep multiple rows during PIVOT in Snowflake

I would like to transpose rows into columns in Snowflake.

Suppose I have the following table BASE

ID	value	type
1	100	‘A’
1	200	‘B’
1	300	‘B’
2	400	‘A’

The output should be as follows:

ID	A	B
1	100	200
1	100	300
2	400	NULL

Currently I am pivoting the table with

SELECT ID,
CASE WHEN TYPE = 'A' THEN VALUE ELSE NULL AS A,
CASE WHEN TYPE = 'B' THEN VALUE ELSE NULL AS B
FROM BASE

For now the GROUP BY statement is missing. Typically I would GROUP BY ID, but that does not account for keeping one row per each value on the same TYPE and ID.

Any ideas how to achieve this?

Cheers, P

Answer

You can use conditional aggregation. You can use row_number() to get multiple rows:

SELECT ID,
       MAX(CASE WHEN TYPE = 'A' THEN VALUE END) AS A,
       MAX(CASE WHEN TYPE = 'B' THEN VALUE END) AS B
FROM (SELECT B.*,
             ROW_NUMBER() OVER (PARTITION BY ID, TYPE ORDER BY VALUE) as seqnum
      FROM BASE B
     ) B
GROUP BY ID, seqnum;