如何在 Snowflake 的 PIVOT 中使用 LISTAGG

结论

Snowflake 的 PIVOT 不能直接将 LISTAGG 用作聚合函数。可以采用两种写法：

1. 使用条件聚合，在各州对应的列中执行 LISTAGG。
2. 先按州执行 LISTAGG，再用 MAX 将聚合结果透视成列。

推荐写法：条件聚合

SELECT
    transaction_id,
    transaction_type,
    LISTAGG(
        CASE WHEN state = 'CA'
             THEN TO_CHAR(date_timestamp, 'MM/DD/YYYY HH24:MI:SS')
        END,
        ', '
    ) WITHIN GROUP (ORDER BY date_timestamp) AS CA,
    LISTAGG(
        CASE WHEN state = 'NY'
             THEN TO_CHAR(date_timestamp, 'MM/DD/YYYY HH24:MI:SS')
        END,
        ', '
    ) WITHIN GROUP (ORDER BY date_timestamp) AS NY,
    LISTAGG(
        CASE WHEN state = 'MA'
             THEN TO_CHAR(date_timestamp, 'MM/DD/YYYY HH24:MI:SS')
        END,
        ', '
    ) WITHIN GROUP (ORDER BY date_timestamp) AS MA
FROM database.schema.table1
GROUP BY
    transaction_id,
    transaction_type
ORDER BY
    transaction_id,
    transaction_type;

对于不属于目标州的记录，CASE 会返回 NULL，而 LISTAGG 会忽略这些值。WITHIN GROUP (ORDER BY date_timestamp) 可以让同一列中的时间按先后顺序排列。

结果形式如下：

如果必须使用 PIVOT

先完成字符串聚合，让每个 transaction_id、transaction_type 和 state 组合只保留一行，再用 MAX 进行透视：

WITH aggregated AS (
    SELECT
        transaction_id,
        transaction_type,
        state,
        LISTAGG(
            TO_CHAR(date_timestamp, 'MM/DD/YYYY HH24:MI:SS'),
            ', '
        ) WITHIN GROUP (ORDER BY date_timestamp) AS timestamps
    FROM database.schema.table1
    GROUP BY
        transaction_id,
        transaction_type,
        state
)
SELECT *
FROM aggregated
PIVOT (
    MAX(timestamps)
    FOR state IN (
        'CA' AS CA,
        'NY' AS NY,
        'MA' AS MA
    )
)
ORDER BY
    transaction_id,
    transaction_type;

这里的 MAX(timestamps) 不用于比较多个字符串。前面的 CTE 已经保证每个分组和州只有一个 timestamps 值，MAX 只是借助 Snowflake PIVOT 支持的聚合函数，把这个值放入对应的列。

原查询存在的问题

除了不能在 PIVOT 中直接调用 LISTAGG，原查询还使用了：

t1.time

但给出的表结构中，字段名是：

date_timestamp

因此，即使聚合函数能够使用，也需要修正这个字段名。

注意事项

• 如果 date_timestamp 已经是 TIMESTAMP 类型，建议先用 TO_CHAR 明确输出格式，否则隐式转换的结果可能受当前会话格式参数影响。
• 如果该字段实际存储的是字符串，应先使用合适的 TO_TIMESTAMP 或 TRY_TO_TIMESTAMP 格式将其转换为时间，再进行排序。
• LISTAGG 的分隔符可以改为 ', '、'|' 或其他字符串。
• 州列表固定且数量不多时，条件聚合通常比先聚合再使用 PIVOT 更清楚。

备注：内容仅供参考。