Snowflake拆分竖线分隔字符串为列名报错，求正确实现方法

我正在使用Snowflake处理数据，现有一个来自CSV文件、以|作为分隔符的字符串，希望将该字符串拆分后作为另一张表的表头列名。例如执行语句SELECT 'APPLES|BANANAS|PEARS|ORANGES|GRAPES|LEMONS' FROM fruits可得到单条字符串结果，期望将其拆分为APPLES、BANANAS、PEARS、ORANGES、GRAPES、LEMONS这些独立列。

尝试使用split_part函数实现时，报错Error: Invalid identifier 'COLNAME' (line 4)，所用代码如下：

WITH fruits AS
(SELECT 'APPLES|BANANAS|PEARS|ORANGES|GRAPES|LEMONS' AS colname)

SELECT 
SPLIT_PART(colname, '|', 1) AS APPLES,
SPLIT_PART(colname, '|', 2) AS BANANAS,
SPLIT_PART(colname, '|', 3) AS PEARS,
SPLIT_PART(colname, '|', 4) AS ORANGES,
SPLIT_PART(colname, '|', 5) AS GRAPES,
SPLIT_PART(colname, '|', 6) AS LEMONS
FROM fruits;

报错原因分析与修正 #

你提供的代码语法本身是正确的，报错Invalid identifier 'COLNAME'通常由以下几种情况导致：

• 拼写/大小写问题：如果实际运行时将别名colname误写为COLNAME且用双引号包裹（如AS "COLNAME"），而Snowflake会话设置了QUOTED_IDENTIFIERS_IGNORE_CASE = FALSE，则引用时必须保持双引号和大小写一致。
• CTE定义错误：检查CTE的括号是否正确闭合，或是否存在多余的分号打断语句结构。
• 表名冲突：如果数据库中已存在名为fruits的物理表，可能会与CTE的fruits产生歧义（但CTE优先级更高，此情况概率较低）。

修正后的可运行代码与你提供的一致，确认无语法错误：

WITH fruits AS
(SELECT 'APPLES|BANANAS|PEARS|ORANGES|GRAPES|LEMONS' AS colname)

SELECT 
SPLIT_PART(colname, '|', 1) AS APPLES,
SPLIT_PART(colname, '|', 2) AS BANANAS,
SPLIT_PART(colname, '|', 3) AS PEARS,
SPLIT_PART(colname, '|', 4) AS ORANGES,
SPLIT_PART(colname, '|', 5) AS GRAPES,
SPLIT_PART(colname, '|', 6) AS LEMONS
FROM fruits;

更灵活的实现方式（适配列数不固定场景） #

如果你的分隔符字符串列数不固定，或需要动态生成列，推荐使用SPLIT+FLATTEN+PIVOT的组合方案：

固定列数的动态转置

WITH fruits AS
(SELECT 'APPLES|BANANAS|PEARS|ORANGES|GRAPES|LEMONS' AS colname),
split_rows AS
(
  SELECT 
    value AS column_name,
    ROW_NUMBER() OVER () AS rn
  FROM fruits,
  LATERAL FLATTEN(INPUT => SPLIT(colname, '|'))
)
SELECT *
FROM split_rows
PIVOT(MAX(column_name) FOR rn IN (1,2,3,4,5,6))
AS p (APPLES, BANANAS, PEARS, ORANGES, GRAPES, LEMONS);

完全动态的列生成（适配任意列数）

如果列数不确定，可以使用Snowflake存储过程+动态SQL实现：

DECLARE
  col_str STRING;
  pivot_cols STRING;
  sql_stmt STRING;
BEGIN
  -- 获取分隔符字符串
  SELECT colname INTO col_str FROM fruits;
  
  -- 生成PIVOT所需的列序号列表
  SELECT LISTAGG(rn::STRING, ',') INTO pivot_cols
  FROM (SELECT ROW_NUMBER() OVER () AS rn FROM TABLE(SPLIT_TO_TABLE(col_str, '|')));
  
  -- 构造动态SQL语句
  sql_stmt := '
    WITH split_rows AS
    (
      SELECT 
        value AS column_name,
        ROW_NUMBER() OVER () AS rn
      FROM fruits,
      LATERAL FLATTEN(INPUT => SPLIT(colname, ''|''))
    )
    SELECT *
    FROM split_rows
    PIVOT(MAX(column_name) FOR rn IN (' || pivot_cols || '))
    AS p (' || REPLACE(col_str, '|', ',') || ');
  ';
  
  -- 执行动态SQL
  EXECUTE IMMEDIATE sql_stmt;
END;

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