如何在SnowSQL中按列值遍历表并将行合并至Transaction列？

核心思路 #

先通过窗口函数给每条数据标记所属事务组：以message字段开头为V22200的行作为事务起始点，统计当前行及之前的起始行数量作为唯一事务ID，将同属一个事务的行归为一组；再根据需求选择合并为单列或转成多列。

实现方案 #

1. 标记事务ID #

先给每条记录分配事务组ID，确保同一事务内的行拥有相同ID：

WITH labeled_data AS (
    SELECT
        ROWID,
        message,
        -- 统计当前行及之前的V22200起始行数量，作为事务唯一ID
        COUNT(CASE WHEN message LIKE 'V22200%' THEN 1 END) OVER (ORDER BY ROWID) AS transaction_id
    FROM your_table_name
)
SELECT * FROM labeled_data;

2. 方案A：将事务内的行合并为单个字符串列 #

如果需要把事务内所有后续行的message合并成名为Transaction的字符串列（可自定义分隔符）：

WITH labeled_data AS (
    SELECT
        ROWID,
        message,
        COUNT(CASE WHEN message LIKE 'V22200%' THEN 1 END) OVER (ORDER BY ROWID) AS transaction_id
    FROM your_table_name
),
transaction_groups AS (
    SELECT
        transaction_id,
        -- 合并事务内所有message，若只需合并起始行之后的内容，添加WHERE NOT message LIKE 'V22200%'
        LISTAGG(message, '|') WITHIN GROUP (ORDER BY ROWID) AS Transaction
    FROM labeled_data
    GROUP BY transaction_id
)
-- 将合并结果关联回原表，仅展示每个事务的起始行及对应合并内容
SELECT
    ld.ROWID,
    ld.message,
    tg.Transaction
FROM labeled_data ld
JOIN transaction_groups tg ON ld.transaction_id = tg.transaction_id
WHERE ld.message LIKE 'V22200%';

3. 方案B：将事务内的行转为单独列（行转列） #

如果需要把事务内的后续行分别转为独立列（如Transaction_1、Transaction_2等），使用PIVOT函数实现：

WITH labeled_data AS (
    SELECT
        ROWID,
        message,
        transaction_id,
        -- 标记每行在事务内的位置，1为起始行，后续行依次递增
        ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY ROWID) AS row_in_transaction
    FROM (
        SELECT
            ROWID,
            message,
            COUNT(CASE WHEN message LIKE 'V22200%' THEN 1 END) OVER (ORDER BY ROWID) AS transaction_id
        FROM your_table_name
    )
),
pivoted_data AS (
    SELECT
        transaction_id,
        "1" AS transaction_start,
        "2" AS Transaction_1,
        "3" AS Transaction_2,
        "4" AS Transaction_3,
        "5" AS Transaction_4,
        "6" AS Transaction_5,
        "7" AS Transaction_6
    FROM labeled_data
    PIVOT (
        MAX(message) FOR row_in_transaction IN (1,2,3,4,5,6,7)
    ) AS p
)
SELECT * FROM pivoted_data;

说明：

• row_in_transaction用于标记行在事务内的顺序，1对应起始行（V22200开头），2及以后为后续行。
• PIVOT中的IN子句需根据业务中事务的最大行数调整，若事务内最多有6个后续行，就写到7（包含起始行）。

注意事项 #

• 确保ROWID是严格递增的，保证事务内的行顺序正确；若ROWID无序，需替换为其他有序字段（如时间戳）。
• 方案A中若message包含分隔符，需更换无冲突的分隔符避免数据混淆。
• 方案B中事务行数不固定时，未填充的列会显示NULL，属于正常现象。

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