BigQuery中Unnest与Pivot列：多键列转行格式转换需求

核心思路 #

因为有50个不同的column.key，手动编写PIVOT列效率极低，直接用动态SQL自动生成所有Key对应的列，同时以row和id作为分组标识，确保每个(row, id)组合的所有Key值都被完整保留（包括id重复但Key不同的实例）。

实现代码 #

DECLARE pivot_columns STRING;

-- 第一步：自动获取所有distinct的key，生成PIVOT需要的列表达式
SET pivot_columns = (
  SELECT STRING_AGG(DISTINCT CONCAT('`', column.key, '`'), ', ')
  FROM `你的项目名.数据集名.原表名`
);

-- 第二步：构造并执行动态PIVOT SQL
EXECUTE IMMEDIATE FORMAT("""
  SELECT 
    row,
    id,
    %s
  FROM (
    SELECT 
      row,
      id,
      column.key AS key_name,
      -- 这里根据实际值类型调整，比如有int_value/bool_value就加进COALESCE
      COALESCE(column.value.string_value, CAST(column.value.int_value AS STRING)) AS key_value
    FROM `你的项目名.数据集名.原表名`
  )
  PIVOT (
    MAX(key_value) FOR key_name IN (%s)
  )
""", pivot_columns, pivot_columns);

关键说明 #

1. 动态列生成：通过STRING_AGG自动拼接所有唯一的column.key，用反引号包裹避免Key包含特殊字符（比如空格、关键字）导致语法错误。
2. 保留所有实例：以row和id作为分组基础，PIVOT时每个(row, id)组合会单独生成一行，完全保留id重复但Key不同的记录。
3. 多类型值处理：用COALESCE统一处理不同类型的value列（比如string_value、int_value），如果不需要转字符串，可根据Key对应的实际类型调整（比如数值型直接用column.value.int_value）。
4. 性能优化：如果原表数据量极大，可先筛选需要的字段或分区查询，减少动态SQL的处理压力。

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