如何在BigQuery中基于键值对生成新列（行转列）

方案一：静态PIVOT（推荐，高效简洁） #

由于键数量较少且固定，BigQuery原生的PIVOT语法是最优选择，性能和可读性都拉满。直接将长表按id聚合，把key转成列：

-- 创建宽表
CREATE OR REPLACE TABLE `your-project.your-dataset.wide_table` AS
SELECT *
FROM `your-project.your-dataset.table_a`
PIVOT (
  ANY_VALUE(value)  -- 每个id+key组合唯一，用MAX/ANY_VALUE都能正确取值
  FOR key IN ('abc', 'def', 'xyz')  -- 列出所有需要转成列的key
);

-- 如果需要物化视图，替换成以下语句
CREATE OR REPLACE MATERIALIZED VIEW `your-project.your-dataset.wide_mv` AS
SELECT *
FROM `your-project.your-dataset.table_a`
PIVOT (
  ANY_VALUE(value)
  FOR key IN ('abc', 'def', 'xyz')
);

注意：ANY_VALUE比MAX更贴合语义，因为我们只是取唯一存在的值，没有聚合需求。后续如果有新key，直接在IN列表里追加即可。

方案二：动态生成PIVOT语句（适配key动态变化） #

如果key偶尔会新增，不想每次手动修改IN列表，可以用动态SQL自动获取所有distinct key并生成PIVOT语句：

DECLARE pivot_keys STRING;

-- 拼接所有distinct key为PIVOT需要的字符串格式
SET pivot_keys = (
  SELECT STRING_AGG(DISTINCT CONCAT("'", key, "'"), ', ')
  FROM `your-project.your-dataset.table_a`
);

-- 执行动态生成的PIVOT SQL
EXECUTE IMMEDIATE FORMAT("""
  CREATE OR REPLACE TABLE `your-project.your-dataset.wide_table` AS
  SELECT *
  FROM `your-project.your-dataset.table_a`
  PIVOT (
    ANY_VALUE(value)
    FOR key IN (%s)
  )
""", pivot_keys);

这个方案会自动适配当前表中所有存在的key，无需手动维护列列表，适合key有少量变动的场景。

方案三：JavaScript UDF实现（备选场景） #

如果必须用UDF实现，可以通过JS将每个id的key-value对封装成结构体，再解析为列：

首先创建自定义JS函数：

CREATE OR REPLACE FUNCTION `your-project.your-dataset.kv_to_struct`(kv_pairs ARRAY<STRUCT<key STRING, value STRING>>)
RETURNS STRUCT<abc STRING, def STRING, xyz STRING>  -- 定义所有目标列
LANGUAGE js AS """
  const result = {};
  kv_pairs.forEach(pair => {
    result[pair.key] = pair.value;
  });
  return result;
""";

然后生成宽表：

CREATE OR REPLACE TABLE `your-project.your-dataset.wide_table` AS
SELECT
  id,
  parsed.abc,
  parsed.def,
  parsed.xyz
FROM (
  SELECT
    id,
    `your-project.your-dataset.kv_to_struct`(ARRAY_AGG(STRUCT(key, value))) AS parsed
  FROM `your-project.your-dataset.table_a`
  GROUP BY id
);

注意：这个方案需要提前在UDF的返回结构体中定义所有可能的key，新增key时必须修改UDF，灵活性不如PIVOT，仅适合必须使用UDF的特殊场景。

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