如何对Snowflake多列表实现动态透视并处理空值

需要对Snowflake中的test表做透视：将name1和name2的所有唯一值作为列，按ID聚合对应value，无匹配值时填充0。表结构、期望输出及已尝试的两种方案如下：

表结构 #

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

期望输出 #

已尝试方案 #

1. 分两次透视后关联：未正确聚合导致空值，代码：

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;

2. 硬编码IFF函数：能得到正确结构，但无法动态生成列，必须手动指定所有name值：

SELECT ID
, MAX(IFF(NAME1= 'X1',VALUE1,0)) AS X1
, MAX(IFF(NAME2= 'X2',VALUE2,0)) AS X2
, MAX(IFF(NAME1= 'X3',VALUE1,0)) AS X3
, MAX(IFF(NAME2= 'X4',VALUE2,0)) AS X4
, MAX(IFF(NAME1= 'X5',VALUE1,0)) AS X5
, MAX(IFF(NAME2= 'X6',VALUE2,0)) AS X6
, MAX(IFF(NAME1= 'X7',VALUE1,0)) AS X7
, MAX(IFF(NAME2= 'X8',VALUE2,0)) AS X8
FROM TEST GROUP BY ID;

用**逆透视（UNPIVOT）+ 透视（PIVOT）**的组合，既能动态生成列，又能自动处理空值填充0：

核心思路 #

先把原表中name1/value1、name2/value2两组列合并成统一的name和value字段（逆透视），让所有name值集中到一个列里，再基于这个统一的数据集做透视，最后用COALESCE把空值替换为0。

静态列实现（适合已知所有name值的场景） #

WITH unpivoted AS (
    SELECT ID, name, value
    FROM test
    UNPIVOT (value FOR name IN (name1, name2))
)
SELECT
    ID,
    COALESCE(X1, 0) AS X1,
    COALESCE(X2, 0) AS X2,
    COALESCE(X3, 0) AS X3,
    COALESCE(X4, 0) AS X4,
    COALESCE(X5, 0) AS X5,
    COALESCE(X6, 0) AS X6,
    COALESCE(X7, 0) AS X7,
    COALESCE(X8, 0) AS X8
FROM unpivoted
PIVOT (MAX(value) FOR name IN ('X1', 'X2', 'X3', 'X4', 'X5', 'X6', 'X7', 'X8'))
ORDER BY ID;

完全动态列实现（自动提取所有name值） #

如果需要自动识别name1和name2中的所有唯一值，不用手动写列名，用Snowflake的动态SQL实现：

-- 第一步：获取所有唯一的name值，拼接成透视需要的格式
SET all_unique_names = (
    SELECT LISTAGG(DISTINCT '''' || name || '''', ', ')
    FROM (
        SELECT name1 AS name FROM test
        UNION ALL
        SELECT name2 AS name FROM test
    )
);

-- 第二步：动态生成并执行透视SQL
EXECUTE IMMEDIATE $$
WITH unpivoted AS (
    SELECT ID, name, value
    FROM test
    UNPIVOT (value FOR name IN (name1, name2))
)
SELECT
    ID,
    -- 动态生成每个name对应的COALESCE列
    (SELECT LISTAGG('COALESCE(' || name || ', 0) AS ' || name, ', ') WITHIN GROUP (ORDER BY name)
     FROM (SELECT DISTINCT name FROM unpivoted))
FROM unpivoted
PIVOT (MAX(value) FOR name IN ($all_unique_names))
GROUP BY ID
ORDER BY ID;
$$;

方案优势 #

• 避免了两次透视关联带来的空值问题，逆透视后统一处理数据更简洁
• 动态SQL版本完全不用硬编码列名，自动适配name1和name2的新增值
• 通过COALESCE确保无匹配值的列自动填充为0

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