Snowflake: How to include NULL value in the UNPIVOT

Question

Requirement: Include NULL values in the UNPIVOT

I have a table below:

Month   Col_1   Col_2   Col_3   Col_4   Col_5   
---------------------------------------------
Jan     NULL    NULL    1       1       1   

I want to unpivot this table

My query:

select Month, Name, value from 
TableName
    unpivot
    (
        Value 
        for Name in (Col_1,Col_2,Col_3,Col_4,Col_5)  
    ) u 

Result:

This results without the NULL values below:

Month    Name    Value
-----------------------
Jan      Col_3   1
Jan      Col_4   1
Jan      Col_5   1

Expected Result:

I want the NULLs to be included in the result

Month    Name    Value
-----------------------
Jan      Col_1   NULL
Jan      Col_2   NULL
Jan      Col_3   1
Jan      Col_4   1
Jan      Col_5   1

Any help would be appreciated in Snowflake

Answer 1

as a workaround if you are not expecting negative 1 in any of your columns.

with cte as (select 
                  month
                  ,ifnull(col_1,-1) col_1
                  ,ifnull(col_2,-1) col_2
                  ,ifnull(col_3,-1) col_3
                  ,ifnull(col_4,-1) col_4
                  ,ifnull(col_5,-1) col_5
                   from UNPIVOT_TEST
                                  )
                                  select 
                                  month
                                  ,name
                                  ,case when value=-1 then NULL else value end as value 
                                  from cte unpivot
                                  ( 
                                  value for name in ( col_1,col_2,col_3,col_4,col_5)
                                  );