Snowflake将逗号分隔单列值拆分为多列的技术问题

假设你的阶段表（如stage_table）仅包含一列（如raw_data），其中首行是逗号分隔的列名，第二行是对应业务数据。以下是可行的解决方案：

方案1：静态列名映射（已知列名时） #

通过拆分列名和数据行，利用位置关联后用CASE语句映射到对应列：

WITH split_headers AS (
    -- 拆分首行列名，记录每个列名的位置
    SELECT 
        SPLIT_PART(raw_data, ',', seq) AS column_name,
        seq AS column_position
    FROM stage_table,
        TABLE(SPLIT_TO_TABLE(raw_data, ','))
    WHERE ROW_NUMBER() OVER (ORDER BY raw_data) = 1
),
split_data AS (
    -- 拆分第二行数据，记录每个值的位置
    SELECT 
        SPLIT_PART(raw_data, ',', seq) AS value,
        seq AS column_position
    FROM stage_table,
        TABLE(SPLIT_TO_TABLE(raw_data, ','))
    WHERE ROW_NUMBER() OVER (ORDER BY raw_data) = 2
)
-- 根据列名映射值
SELECT 
    MAX(CASE WHEN sh.column_name = 'user_id' THEN sd.value END) AS user_id,
    MAX(CASE WHEN sh.column_name = 'user_name' THEN sd.value END) AS user_name,
    MAX(CASE WHEN sh.column_name = 'register_date' THEN sd.value END) AS register_date
    -- 按需添加更多列的CASE语句
FROM split_headers sh
JOIN split_data sd ON sh.column_position = sd.column_position;

方案2：动态列名映射（列名不固定时） #

如果列名是动态变化的，使用动态SQL自动生成Pivot语句：

-- 1. 获取列名字符串，用于后续Pivot
SET header_list = (
    SELECT LISTAGG('''' || value || '''', ',')
    FROM stage_table,
        TABLE(SPLIT_TO_TABLE(raw_data, ','))
    WHERE ROW_NUMBER() OVER (ORDER BY raw_data) = 1
);

-- 2. 构建动态Pivot SQL
SET dynamic_pivot_sql = '
WITH split_headers AS (
    SELECT seq AS pos, value AS col_name
    FROM stage_table, TABLE(SPLIT_TO_TABLE(raw_data, '',''))
    WHERE ROW_NUMBER() OVER (ORDER BY raw_data) = 1
),
split_data AS (
    SELECT seq AS pos, value AS col_value
    FROM stage_table, TABLE(SPLIT_TO_TABLE(raw_data, '',''))
    WHERE ROW_NUMBER() OVER (ORDER BY raw_data) = 2
)
SELECT *
FROM (
    SELECT sh.col_name, sd.col_value
    FROM split_headers sh
    JOIN split_data sd ON sh.pos = sd.pos
)
PIVOT (
    MAX(col_value) FOR col_name IN ('|| $header_list ||')
) AS pivot_result;
';

-- 3. 执行动态SQL
EXECUTE IMMEDIATE $dynamic_pivot_sql;

关键说明 #

之前使用SPLIT_TO_TABLE+Pivot未达预期，核心原因是没有通过位置关联列名和对应数据。上述方案通过seq（拆分后的位置序号）将列名和数据值一一绑定，确保映射关系正确，再通过静态CASE或动态Pivot生成目标格式。

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