BigQuery中将多列转多行的动态SQL实现问题

问题场景 #

你有一张包含100多列的表materialtable，需要保留material和plant作为固定列，将其余所有列转换为column_name（列名）和column_value（列值）的行结构。原方案用UNION ALL手动拼接每个列的查询，代码冗长且不易维护，尝试动态SQL时出现语法错误：Syntax error: Unclosed string literal at。

错误原因分析 #

你尝试的动态SQL中，使用"转义双引号是错误的。在BigQuery中：

1. 字符串字面量用单引号包裹，而非双引号（双引号用于标识符，比如列名、表名）
2. 字符串内的单引号需要用两个单引号转义，而非HTML实体"
3. 原代码中SET语句末尾缺少分号，也可能触发语法错误

修正后的动态SQL方案 #

以下是修复后的动态SQL代码，可自动生成所有非固定列的转行逻辑：

DECLARE query STRING;

SET query = (
  SELECT CONCAT(
    'SELECT * FROM (',
    STRING_AGG(
      FORMAT(
        'SELECT 
           material,
           plant,
           ''%s'' AS column_name,
           CAST(%s AS STRING) AS column_value
         FROM `%s.%s.%s`',
        column_name,
        column_name,
        table_catalog,
        table_schema,
        table_name
      ),
      ' UNION ALL '
    ),
    ')'
  )
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'materialtable'
    AND column_name NOT IN ('material', 'plant')
  ORDER BY ordinal_position
);

EXECUTE IMMEDIATE query;

关键修正点 #

• 用''%s''替代"%s"，确保生成的SQL中column_name是合法的字符串值（比如'name'）
• 拼接完整的表路径（table_catalog.table_schema.table_name），避免多数据集环境下的表歧义
• 补充SET语句末尾的分号，修复语法问题

更高效的原生方案：UNPIVOT #

BigQuery原生支持UNPIVOT语法，比UNION ALL更简洁高效，结合动态SQL可自动适配所有列：

DECLARE query STRING;

SET query = (
  SELECT CONCAT(
    'SELECT 
       material,
       plant,
       column_name,
       CAST(column_value AS STRING) AS column_value
     FROM `', table_catalog, '.', table_schema, '.', table_name, '`
     UNPIVOT(
       column_value FOR column_name IN (',
    STRING_AGG(column_name, ', ' ORDER BY ordinal_position),
    ')
     )'
  )
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'materialtable'
    AND column_name NOT IN ('material', 'plant')
  LIMIT 1
);

EXECUTE IMMEDIATE query;

优势 #

• 原生语法执行效率更高，避免多次扫描表（UNION ALL会多次读取原表）
• 代码逻辑更清晰，符合列转行的语义化表达

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