如何在Snowflake中解析单列逗号分隔值并转为以两位前缀为列的表？

根据你的需求，我们需要解析message列的每行数据，提取每个值的两位前缀作为新列并生成新表，以下分两种场景提供实现代码：

场景1：已知前缀列表 #

如果你已经明确所有可能的两位前缀，可以直接使用正则提取生成列：

假设原表名为original_table，包含id（主键）和message列，message中的值以空格分隔（如AB123 CD456 EF789），代码如下：

CREATE OR REPLACE TABLE new_table AS
SELECT
    id,
    -- 提取AB前缀对应的值（前缀后内容）
    REGEXP_SUBSTR(message, 'AB(\\w+)') AS AB,
    -- 提取CD前缀对应的值
    REGEXP_SUBSTR(message, 'CD(\\w+)') AS CD,
    -- 提取EF前缀对应的值
    REGEXP_SUBSTR(message, 'EF(\\w+)') AS EF
FROM original_table;

如果message中的值带有分隔符（如AB:123 CD:456），可调整正则表达式适配：

REGEXP_SUBSTR(message, 'AB:(\\w+)') AS AB

场景2：动态识别所有前缀（未知前缀列表） #

如果前缀不固定，需要自动识别所有唯一前缀并生成对应列，可使用Snowflake的动态SQL+透视表实现：

DECLARE
    prefix_list STRING;
BEGIN
    -- 第一步：提取所有唯一的两位前缀
    SELECT LISTAGG(DISTINCT '''' || LEFT(value, 2) || '''', ',') INTO prefix_list
    FROM original_table,
    LATERAL SPLIT_TO_TABLE(message, ' '); -- 这里的空格是message内值的分隔符，按需调整

    -- 第二步：动态生成透视表SQL并执行
    EXECUTE IMMEDIATE '
        CREATE OR REPLACE TABLE new_table AS
        SELECT *
        FROM (
            SELECT
                id,
                LEFT(value, 2) AS prefix,
                SUBSTR(value, 3) AS value_content -- 提取前缀后的内容，按需调整
            FROM original_table,
            LATERAL SPLIT_TO_TABLE(message, '' '')
        )
        PIVOT (
            MAX(value_content) FOR prefix IN (' || prefix_list || ')
        )
    ';
END;

关键说明： #

• 若message内的值使用其他分隔符（如逗号、分号），需修改SPLIT_TO_TABLE的第二个参数。
• 若同一行同一前缀对应多个值，可将PIVOT中的MAX替换为LISTAGG(value_content, ',')来合并所有值。
• 执行动态SQL需要对应的数据权限。

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