Snowflake中如何实现超限定列数后同ID数据转行输出？

标准PIVOT无法实现同ID数据超过指定列数后换行的需求，因为它会将同一ID的所有值聚合到单行的不同列中。要实现你要的效果，需要通过窗口函数分组+透视的组合方案，具体步骤如下：

实现思路 #

1. 给每个ID下的记录按顺序分配全局序号，确定每个值的位置；
2. 根据指定的最大列数（示例中为2），将同一ID的记录划分为多个行组（每N个值一组，N为列数）；
3. 给每个行组内的记录分配列序号（1到N）；
4. 基于ID和行组进行透视，将组内的列序号转为输出列，超过组容量的值自动转到新行。

示例SQL代码 #

假设原表名为test_table，包含ID和Value两列：

WITH ranked_data AS (
    -- 给每个ID下的记录按Value排序并分配全局序号
    SELECT 
        ID,
        Value,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Value) AS global_row_num
    FROM test_table
),
grouped_data AS (
    -- 划分行组并分配组内列序号
    SELECT 
        ID,
        Value,
        -- 每2个值为一组（可修改为5或其他指定列数）
        CEILING(global_row_num / 2) AS row_group,
        -- 组内列序号：1到2
        MOD(global_row_num - 1, 2) + 1 AS col_num
    FROM ranked_data
)
-- 透视得到最终结果
SELECT 
    ID,
    "1" AS Col1,
    "2" AS Col2
FROM grouped_data
PIVOT (
    MAX(Value)
    FOR col_num IN (1, 2) -- 列序号范围需和指定列数一致
) AS pivot_result
ORDER BY ID, row_group;

调整列数的方法 #

如果需要将最大列数改为5，只需修改3处：

1. 将CEILING(global_row_num / 2)中的2改为5；
2. 将MOD(global_row_num - 1, 2) + 1中的2改为5；
3. 将PIVOT中的IN (1, 2)改为IN (1, 2, 3, 4, 5)，同时SELECT中的别名对应改为Col1到Col5。

效果验证 #

针对你的示例数据，执行上述代码后会输出：

完全符合你期望的输出格式。

内容的提问来源于stack exchange，提问作者Randy B.