如何在Snowflake中按分隔符动态将行转换为列？

问题场景 #

现有Snowflake表，其中某列存储多个以/分隔的值，每行值的数量不固定，需要将这些分隔后的值动态转换为多列（列数由最多值的行决定）。

示例数据 #

假设表名为source_table，结构及数据如下：

期望结果 #

解决方案：使用动态SQL+存储过程实现 #

由于每行值的数量不固定，静态PIVOT无法直接适配，需通过动态SQL生成适配的列数。以下是封装好的存储过程实现：

1. 创建存储过程

CREATE OR REPLACE PROCEDURE dynamic_pivot_split_col()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
  max_cols INTEGER;
  pivot_cols VARCHAR;
  dynamic_sql VARCHAR;
BEGIN
  -- 获取分割后最多的列数
  SELECT MAX(ARRAY_SIZE(SPLIT(values_col, '/'))) INTO max_cols FROM source_table;
  
  -- 生成动态列名（col1, col2...colN）
  pivot_cols := LISTAGG('col' || seq4(), ', ') WITHIN GROUP (ORDER BY seq4())
                FROM TABLE(GENERATOR(ROWCOUNT => :max_cols));
  
  -- 构造动态转换SQL
  dynamic_sql := '
    WITH split_rows AS (
      SELECT 
        id,
        STRTOK(values_col, ''/'', seq4()+1) AS split_value,
        ''col'' || (seq4()+1) AS column_name
      FROM source_table,
           TABLE(GENERATOR(ROWCOUNT => :max_cols))
      WHERE seq4() < ARRAY_SIZE(SPLIT(values_col, ''/''))
    )
    SELECT *
    FROM split_rows
    PIVOT (
      MAX(split_value) FOR column_name IN (' || pivot_cols || ')
    ) AS pivot_result
    ORDER BY id;
  ';
  
  -- 执行动态SQL
  EXECUTE IMMEDIATE :dynamic_sql;
  
  RETURN '转换完成，共生成' || max_cols || '列';
END;
$$;

2. 执行存储过程

CALL dynamic_pivot_split_col();

关键逻辑说明 #

• 获取最大列数：用ARRAY_SIZE(SPLIT(values_col, '/'))计算每行分割后的元素数量，取最大值确定需要生成的列数。
• 动态生成列名：通过GENERATOR生成连续序列，拼接成col1、col2格式的列名列表。
• 拆分+转置：先将每个分隔值拆分为单行并标记对应列名，再通过PIVOT将行转成列，MAX函数仅用于聚合（每个id+column_name唯一，聚合不影响结果）。

注意事项 #

• 如果values_col存在空值或空字符串，建议在split_rows的WHERE条件中追加AND values_col IS NOT NULL AND values_col != ''过滤无效数据。
• GENERATOR的ROWCOUNT直接使用max_cols，避免生成多余行，提升效率。

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