Snowflake SQL行转列问题求助：将Table A转为指定输出格式

需求分析 #

需要将原表中每行的(id_text_a, text_a)和(id_text_b, text_b)拆分为独立行，同时按以下规则生成连续的id：

1. 按req_id和N分组
2. 原表行按id_text_a升序排列，每组内先输出该行的a字段对，再输出b字段对
3. 最终为每组内的所有拆分记录生成连续递增的id

实现方案 #

使用UNION ALL拆分每行的两组字段，再通过窗口函数ROW_NUMBER()生成连续id，具体SQL如下：

SELECT
    req_id,
    N,
    ROW_NUMBER() OVER(PARTITION BY req_id, N ORDER BY sort_key, type_order) AS id,
    id_text,
    text
FROM (
    -- 拆分id_text_a/text_a为独立行
    SELECT 
        req_id,
        N,
        id_text_a AS id_text,
        text_a AS text,
        id_text_a AS sort_key, -- 用于排序原表行
        1 AS type_order -- 标记为a类型，排在b类型前
    FROM TableA
    UNION ALL
    -- 拆分id_text_b/text_b为独立行
    SELECT 
        req_id,
        N,
        id_text_b AS id_text,
        text_b AS text,
        id_text_a AS sort_key, -- 复用原表行的id_text_a作为排序依据
        2 AS type_order -- 标记为b类型
    FROM TableA
) t
ORDER BY req_id, N, id;

逻辑说明 #

1. 拆分数据：通过UNION ALL将每行的两组字段拆分为两条独立记录，同时保留原行的id_text_a作为排序键，用type_order标记是a还是b字段组。
2. 排序与生成id：用ROW_NUMBER()窗口函数，按req_id和N分区，先按sort_key（原行id_text_a）升序排列原行，再按type_order确保每组内a字段行排在b字段行前，最终生成连续的id。

替代方案（使用LATERAL FLATTEN） #

Snowflake支持使用LATERAL FLATTEN处理数组拆分，代码更简洁：

SELECT
    req_id,
    N,
    ROW_NUMBER() OVER(PARTITION BY req_id, N ORDER BY id_text_a, f.index) AS id,
    f.value:id_text::INT AS id_text,
    f.value:text::STRING AS text
FROM TableA,
LATERAL FLATTEN(
    INPUT => ARRAY_CONSTRUCT(
        OBJECT_CONSTRUCT('id_text', id_text_a, 'text', text_a),
        OBJECT_CONSTRUCT('id_text', id_text_b, 'text', text_b)
    ),
    WITH_OFFSET => TRUE
) f
ORDER BY req_id, N, id;

这里将每行的两组字段转为数组中的对象，FLATTEN拆分后用index区分a/b顺序，再按同样规则排序生成id。

内容的提问来源于stack exchange，提问作者Mado.h