Snowflake：如何在不聚合的情况下转置多列

问题场景 #

原表为键值对结构，同一Common_code下存在多组重复的字段键（如ABC123对应两组Name/City/Sex/Country），需要转置为宽表且不丢失任何记录：

原表数据：

Combined1 Combined2 Common_code
Name       John      ABC123
City       NY        ABC123
Sex        M         ABC123
Country    USA       ABC123
Name       John      ABC123
City       BF        ABC123
Sex        M         ABC123
Country    USA       ABC123
Name       Lucy      XYZ456
City       CB        XYZ456
Sex        F         XYZ456
Country    UK        XYZ456

目标结果：

Name City Sex Country Common_code
John NY   M   USA     ABC123
John BF   M   USA     ABC123
Lucy CB   F   UK      XYZ456

解决方案 #

直接使用PIVOT会合并同一Common_code下的重复组，因此需要先给每组键值对添加分组序号，再执行转置操作：

完整SQL代码 #

-- 给原表添加分组序号，区分同一Common_code下的重复数据组
WITH numbered_data AS (
    SELECT 
        Combined1,
        Combined2,
        Common_code,
        ROW_NUMBER() OVER (PARTITION BY Common_code, Combined1 ORDER BY NULL) AS grp
    FROM your_table_name -- 替换为你的实际表名
)
-- 基于分组序号和Common_code执行PIVOT转置
SELECT 
    Name,
    City,
    Sex,
    Country,
    Common_code
FROM numbered_data
PIVOT (
    ANY_VALUE(Combined2) FOR Combined1 IN ('Name' AS Name, 'City' AS City, 'Sex' AS Sex, 'Country' AS Country)
) AS pivot_result
ORDER BY Common_code, grp;

代码说明 #

1. 分组序号生成：

   • 使用ROW_NUMBER()窗口函数，按Common_code和Combined1分区，给每个重复的键值对组分配唯一序号grp。比如ABC123下的两个Name行，会分别得到grp=1和grp=2，以此区分两组独立数据。
   • ORDER BY NULL用于生成无特定顺序的序号，若有业务排序需求，可替换为实际字段（如数据插入时间）。

2. PIVOT转置：

   • 以grp和Common_code作为分组依据，将Combined1的取值转为列名，对应提取Combined2的值。
   • 使用ANY_VALUE()聚合函数是因为PIVOT必须配合聚合操作，而每个分组下Combined1对应的Combined2是唯一的，MAX()/MIN()也可达到相同效果。

执行上述SQL后，即可得到符合要求的宽表结构，且完整保留所有原始记录。

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