Snowflake: How to include NULL value in the UNPIVOT

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

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)
                                  );

Instead of using unpivot, you can use lateral flatten for this, by creating an object containing the key/values for your column names and values. This solution also dynamically assembles the column names you want to unpivot on, which may be helpful if you have a lot of columns to manage, or the source table might change over time.

First of all create the test data from your example:

create or replace view test_data as
select *
from (VALUES ('Jan', NULL, NULL, 1, 1, 1) ) as 
               raw_data (Month, Col_1, Col_2, Col_3, Col_4, Col_5);

Then :

with 
kc_r1 as (
         -- Return a 1 row result containing only the columns that you want to unpivot on. 
         -- 2 examples below.
         select * EXCLUDE month from test_data limit 1
         -- select * ILIKE 'COL_%' from test_data limit 1 
         ),
kca as (
   select 
      -- Create an array containing the column names required in the 'unpivot'
      object_keys(object_construct_keep_null(*)) k_array
   from kc_r1
),
o as (
   select 
      month,
      object_pick(object_construct_keep_null(test_data.*), kca.k_array) k_v 
   from kca, test_data)
Select 
   month "Month",
   key "Name",
   value "Value"
from kca, o, lateral flatten (input => k_v)
;

Not sure if the column names in your example reflect your actual data but some of the column names your using are reserved words, which is not good practice.

I believe this will work in snowflake (although I'm assuming concat() is a valid function)

with numbers as (
 select 1 as n union all
 select 2 as n union all
 select 3 as n union all
 select 4 as n union all
 select 5 as n
 )
select
  t.Month
, concat('col_', numbers.n) as name
, case
    when numbers.n = 1 then col_1
    when numbers.n = 2 then col_2
    when numbers.n = 3 then col_3
    when numbers.n = 4 then col_4
    when numbers.n = 5 then col_5
  end as value
from tablename t
cross join numbers 

The cross join to 5 rows does the needed row expansion, then we can use a case expression to put all values into 1 column. You could also use a case expression for name if you can't use the concatenation approach.