Snowflake无聚合需求下实现行转列的方法咨询

形式一：按ID聚合为逗号分隔的JSON字符串 #

你的现有代码可以简化，既然表中已经有现成的json列，无需再用OBJECT_CONSTRUCT重新构造对象，直接将json转为字符串后用LISTAGG聚合即可：

SELECT 
    id, 
    LISTAGG(TO_VARCHAR(json), ', ') AS all
FROM SCHEMA.TABLE
WHERE *my conditions*
GROUP BY id;

如果需要更规范的JSON数组格式（后续处理更方便），可以用ARRAY_AGG直接聚合JSON对象再转字符串：

SELECT 
    id, 
    TO_VARCHAR(ARRAY_AGG(json)) AS all
FROM SCHEMA.TABLE
WHERE *my conditions*
GROUP BY id;

结果会是[{"a":"1"},{"a":"2"}]这种标准数组结构，比单纯逗号分隔更易解析。

形式二：行转列（将同ID的JSON拆分为多列） #

要实现行转列，可通过窗口函数标记行号，再用条件判断生成对应列：

WITH ranked_data AS (
    SELECT 
        id,
        json,
        ROW_NUMBER() OVER(PARTITION BY id ORDER BY json) AS rn
    FROM SCHEMA.TABLE
    WHERE *my conditions*
)
SELECT 
    id,
    CASE WHEN rn = 1 THEN json END AS type1,
    CASE WHEN rn = 2 THEN json END AS type2
FROM ranked_data
QUALIFY ROW_NUMBER() OVER(PARTITION BY id ORDER BY rn) = 1;

如果每个ID的行数固定且较少，也可以用PIVOT简化：

SELECT *
FROM (
    SELECT 
        id,
        json,
        'type' || ROW_NUMBER() OVER(PARTITION BY id ORDER BY json) AS col_name
    FROM SCHEMA.TABLE
    WHERE *my conditions*
)
PIVOT (
    MAX(json) FOR col_name IN ('type1', 'type2')
);

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