Snowflake SQL动态循环查询：将用户评论转为带编号的列

假设你的评论表结构类似 user_comments，包含 user_id（用户ID）、comment_text（评论内容），首先需要为每个用户的评论生成排序序号，用来对应 Comment_1、Comment_2 这类列：

-- 先给每个用户的评论添加序号（按评论创建时间排序，可根据实际调整）
WITH ranked_comments AS (
    SELECT
        user_id,
        comment_text,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS comment_order
    FROM user_comments
)

接下来基于已知的最大评论数 max_comments，动态生成对应的列。以下提供两种实现方式：

方式1：使用存储过程动态生成并执行SQL #

创建一个存储过程，传入最大评论数参数，自动拼接出目标SELECT语句并执行：

CREATE OR REPLACE PROCEDURE pivot_comments(max_comments INT)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
    // 初始化列拼接字符串
    let cols = '';
    // 循环生成每个Comment_N列的CASE语句
    for (let i = 1; i <= MAX_COMMENTS; i++) {
        cols += `MAX(CASE WHEN comment_order = ${i} THEN comment_text END) AS Comment_${i}`;
        if (i < MAX_COMMENTS) cols += ',\n    ';
    }
    // 拼接完整SQL语句
    let sql = `
        WITH ranked_comments AS (
            SELECT
                user_id,
                comment_text,
                ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS comment_order
            FROM user_comments
        )
        SELECT
            user_id,
            ${cols}
        FROM ranked_comments
        GROUP BY user_id;
    `;
    // 执行SQL并返回结果
    let stmt = snowflake.createStatement({sqlText: sql});
    let res = stmt.execute();
    return '执行成功，生成的SQL语句：\n' + sql;
$$;

调用存储过程（比如最大评论数是8）：

CALL pivot_comments(8);

方式2：纯SQL拼接生成列定义（适合手动执行或嵌入脚本） #

如果不需要自动执行，只是生成SQL语句，可以用以下方式拼接列部分：

-- 假设max_comments已赋值（比如通过查询获取）
SET max_comments = (SELECT MAX(comment_count) FROM (SELECT user_id, COUNT(*) AS comment_count FROM user_comments GROUP BY user_id) t);

-- 生成列的CASE语句片段
WITH generate_cols AS (
    SELECT 'MAX(CASE WHEN comment_order = ' || seq || ' THEN comment_text END) AS Comment_' || seq AS col_def
    FROM TABLE(GENERATOR(ROWCOUNT => $max_comments)) t,
         TABLE(SEQUENCE(1, $max_comments)) s(seq)
)
-- 拼接完整SQL并输出
SELECT 'WITH ranked_comments AS (
    SELECT
        user_id,
        comment_text,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS comment_order
    FROM user_comments
)
SELECT
    user_id,
    ' || LISTAGG(col_def, ',\n    ') || '
FROM ranked_comments
GROUP BY user_id;' AS final_sql
FROM generate_cols;

执行后会输出完整的SELECT语句，复制后即可运行得到预期的列结构。

关键说明 #

• comment_order 的排序规则可根据实际需求调整（比如按评论ID、更新时间等），确保每个用户的评论顺序符合预期。
• 如果评论数为0或少于max_comments，对应列会显示为NULL，可根据需要用 COALESCE 替换为默认值（比如 COALESCE(MAX(...), '') AS Comment_N）。

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