Snowflake SQL基于可变分组实现行转列（Pivot）的技术问询

需要将初始数据表转置，使得每个fips、yr、id的唯一组合对应多个status字段（命名为status1至status?），status的数量不固定，随数据动态变化。尝试使用Pivot语法但未成功，官方文档中的Pivot需要配合聚合函数，但我只需要完成数据转置。

create or replace temporary table try1
(fips integer, yr integer, id varchar(5), status varchar(10));

insert into try1
values
(20,2022,47250,'03 ACTIVE'),
(20,2022,47250,'79 TERM'),
(20,2023,47250,'79 TERM'),
(20,2022,56957,'83 TERM'),
(20,2023,56957,'03 ACTIVE'),
(20,2023,56957,'83 TERM'),
(20,2022,57645,'79 TERM'),
(20,2023,57645,'03 ACTIVE'),
(20,2023,57645,'79 TERM'),
(20,2023,57645,'83 TERM');

尝试1 #

select *, 1 as flag from try1
pivot(sum(flag)) for status in (any order status)
order by id;

尝试2 #

select *, 1 as flag from try1
pivot(flag) for status in (any order status)
order by id;

尝试3 #

select * from try1
pivot(for status in (any order status))
order by id;

静态Pivot语法要求提前指定列名，无法动态适配数量变化的status，因此需要通过动态SQL实现。以下是基于Snowflake的完整方案（适配你的语法特征）：

实现逻辑 #

先给每个fips/yr/id分组内的status按顺序编号，再通过动态生成的Pivot语句将行转成动态数量的列。

完整动态SQL代码 #

declare
    max_status_cnt integer;
    pivot_rn_def string;
    pivot_select_cols string;
begin
    -- 获取单个分组下最多的status数量
    select max(status_count) into max_status_cnt
    from (select count(*) as status_count from try1 group by fips, yr, id);

    -- 生成Pivot中rn的映射定义（如1 as status1, 2 as status2...）
    select listagg(rn || ' as status' || rn, ', ') within group(order by rn) into pivot_rn_def
    from table(generator(rowcount => :max_status_cnt)) t,
         table(flatten(input => array_generate_range(1, :max_status_cnt + 1))) rn;

    -- 生成查询结果的列名（如status1, status2...）
    select listagg('status' || rn, ', ') within group(order by rn) into pivot_select_cols
    from table(generator(rowcount => :max_status_cnt)) t,
         table(flatten(input => array_generate_range(1, :max_status_cnt + 1))) rn;

    -- 执行动态生成的Pivot语句
    execute immediate '
        select fips, yr, id, ' || pivot_select_cols || '
        from (
            select 
                fips, 
                yr, 
                id, 
                status,
                row_number() over(partition by fips, yr, id order by status) as rn
            from try1
        )
        pivot(
            max(status) for rn in (' || pivot_rn_def || ')
        )
        order by id, yr;
    ';
end;

关键说明 #

1. 用row_number()给每个分组内的status排序编号，确保每个编号对应唯一的status值
2. max(status)是占位聚合函数，因为每个分组内的rn唯一，聚合后就是原status值
3. 动态生成列定义，自动适配数据中最多的status数量，无需手动修改列名

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