Snowflake中如何在UNPIVOT操作中包含NULL值

Snowflake原生UNPIVOT语法默认会过滤值为NULL的行，要实现保留NULL的行转列需求，可采用以下几种方案：

方案1：使用LATERAL FLATTEN（推荐，适合列数适中场景） #

通过构造包含列名和对应值的对象数组，再用FLATTEN展开数组，NULL值会被完整保留：

SELECT 
    t.Month,
    f.value:name::VARCHAR AS Name,
    f.value:value AS Value
FROM 
    TableName t,
    LATERAL FLATTEN(
        INPUT => [
            OBJECT_CONSTRUCT('name', 'Col_1', 'value', t.Col_1),
            OBJECT_CONSTRUCT('name', 'Col_2', 'value', t.Col_2),
            OBJECT_CONSTRUCT('name', 'Col_3', 'value', t.Col_3),
            OBJECT_CONSTRUCT('name', 'Col_4', 'value', t.Col_4),
            OBJECT_CONSTRUCT('name', 'Col_5', 'value', t.Col_5)
        ]
    ) f;

方案2：使用UNION ALL（直观，适合列数较少场景） #

逐个列查询后合并结果，这种方式不会过滤NULL值：

SELECT Month, 'Col_1' AS Name, Col_1 AS Value FROM TableName
UNION ALL
SELECT Month, 'Col_2' AS Name, Col_2 AS Value FROM TableName
UNION ALL
SELECT Month, 'Col_3' AS Name, Col_3 AS Value FROM TableName
UNION ALL
SELECT Month, 'Col_4' AS Name, Col_4 AS Value FROM TableName
UNION ALL
SELECT Month, 'Col_5' AS Name, Col_5 AS Value FROM TableName;

方案3：使用CTE生成列名+CASE匹配（适合列数较多场景） #

先构造所有目标列名的列表，再通过交叉关联和CASE语句匹配对应列的值：

WITH ColumnNames AS (
    SELECT column_name
    FROM VALUES ('Col_1'), ('Col_2'), ('Col_3'), ('Col_4'), ('Col_5') AS t(column_name)
)
SELECT 
    t.Month,
    cn.column_name AS Name,
    CASE cn.column_name
        WHEN 'Col_1' THEN t.Col_1
        WHEN 'Col_2' THEN t.Col_2
        WHEN 'Col_3' THEN t.Col_3
        WHEN 'Col_4' THEN t.Col_4
        WHEN 'Col_5' THEN t.Col_5
    END AS Value
FROM TableName t
CROSS JOIN ColumnNames cn;

以上三种方案均可得到包含NULL值的转换结果。

内容的提问来源于stack exchange，提问作者Karthik