I was trying to retrieve an output on Snowflake table having more than 2 columns to pivot. Below, sharing the code, expected output and the logics I have tried
create or replace table test(ID number, name1 varchar, value1 number, name2 varchar, value2 number)
as select * from values
(1, 'X1', 15, 'X2', 25),
(1, 'X3', 45, 'X4', 65),
(2, 'X1', 35, 'X2', 55),
(2, 'X5', 85, 'X6', 95),
(3, 'X7', 35, 'X8', 55);
Expected output is
| ID | X1 | X2 | X3 | X4 | X5 | X6 | X7 | X8 |
|---|---|---|---|---|---|---|---|---|
| 1 | 15 | 25 | 45 | 65 | 0 | 0 | 0 | 0 |
| 2 | 35 | 55 | 0 | 0 | 85 | 95 | 0 | 0 |
| 3 | 0 | 0 | 0 | 0 | 0 | 0 | 65 | 15 |
I tried the below 2 logics
1st Logic – this output is not doing GROUP BY on the ID column and creates NULLs
SELECT pvt1.*, pvt2.* (EXCLUDE pvt2.ID) FROM
select pvt1.*, pvt2.* exclude ID from
(SELECT * EXCLUDE(name2,value2)
FROM test
PIVOT(MAX(value1) FOR name1 IN (ANY ORDER BY name1))
) pvt1
JOIN
(SELECT * EXCLUDE(name1,value1)
FROM test
PIVOT(MAX(value2) FOR name2 IN (ANY ORDER BY name2))
) pvt2 on pvt1.ID = pvt2.ID
;
2nd Logic – this output is generating the output but columns are not getting dynamically generated, I am hardcoding the column names(X1_COL, X2_COL) manually here
SELECT ID
, MAX(IFF(NAME1= 'X1',VALUE1,0)) AS X1_COl
, MAX(IFF(NAME2= 'X2',VALUE2,0)) AS X2_COL
, MAX(IFF(NAME1= 'X3',VALUE1,0)) AS X3_COL
, MAX(IFF(NAME2= 'X4',VALUE2,0)) AS X4_COL
, MAX(IFF(NAME1= 'X5',VALUE1,0)) AS X5_COL
, MAX(IFF(NAME2= 'X6',VALUE2,0)) AS X6_COL
, MAX(IFF(NAME1= 'X7',VALUE1,0)) AS X7_COL
, MAX(IFF(NAME2= 'X8',VALUE2,0)) AS X8_COL
FROM TEST GROUP BY ID;
Please assist with a better way to handle nulls and dynamically pick values from Name1 and Name2 of the table TEST as shown in the expected output
1
Using UNION ALL to unpivot and then dynamic PIVOT:
WITH cte AS (
SELECT ID, name1, value1 FROM test
UNION ALL
SELECT ID, name2, value2 FROM test
)
SELECT *
FROM cte
PIVOT(MAX(value1) FOR name1 IN (ANY ORDER BY name1) DEFAULT ON NULL (0)) pvt1;
Output: