Snowflake SQL PIVOT函数列顺序错位及带引号列名问题咨询

问题原因 #

Snowflake的PIVOT语法默认将原表中所有未参与聚合、未作为透视键的列，按原表从左到右的顺序排列在结果集最前面。你在AS P (NUMBER_ID, COUNTRY)中指定的列名是按上述默认生成的列顺序依次重命名的，并非直接匹配原表的对应列，所以当你指定的列不是原表前两列时，就会出现取值错误、列顺序混乱的问题。

修复列对应错误&顺序混乱问题 #

方案1（推荐）：子查询提前筛选所需列 #

先通过子查询只保留分组用的列、聚合用列、透视列，自行控制列的顺序，再执行PIVOT，不需要额外重命名PIVOT后的表列：

SELECT *
FROM (
  -- 按你需要的顺序明确列出要保留的非透视列，以及透视需要的两个字段
  SELECT NUMBER_ID, COUNTRY, DATETIME, TATUS
  FROM TABLE_A
) AS SRC
PIVOT(MIN(DATETIME) FOR TATUS IN ('claim', 'bought', 'returned')) AS P

该方案执行后，结果前两列就是正确取值的NUMBER_ID、COUNTRY，后续紧跟三个透视生成的列，没有多余列，顺序完全可控。

方案2：显式指定SELECT列顺序 #

如果需要保留原表所有未分组列，不要在AS P后自定义列名，PIVOT执行完成后在SELECT层手动指定列的顺序和取值：

-- 按你想要的顺序明确列出所有结果列
SELECT NUMBER_ID, COUNTRY, "claim", "bought", "returned"
FROM TABLE_A
PIVOT(MIN(DATETIME) FOR TATUS IN ('claim', 'bought', 'returned')) AS P

透视列去掉引号的实现方法 #

不需要将透视生成的列添加到P后面的列名列表，直接在PIVOT的IN子句中给每个透视值指定别名即可：

SELECT *
FROM (
  SELECT NUMBER_ID, COUNTRY, DATETIME, TATUS
  FROM TABLE_A
) AS SRC
PIVOT(
  MIN(DATETIME) 
  FOR TATUS IN (
    'claim' AS claim,
    'bought' AS bought,
    'returned' AS returned
  )
) AS P

执行后生成的claim/bought/returned列均为不带引号的普通标识符，可直接调用。如果透视值包含特殊字符、空格，别名用双引号包裹即可。

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