如何在BigQuery中不使用IN参数实现表的PIVOT操作

你的原始代码存在两个关键问题，导致无法得到预期结果：

1. 聚合目标错误：你使用了sum(list)，但实际需要聚合的是value字段——我们要把对应类别的数值填充到新列中，而非聚合类别名称。
2. PIVOT子句格式错误：FOR list IN (...)中必须明确写出要转为列的list字段的具体取值，不能直接写字段名list。

静态PIVOT实现（已知所有类别） #

如果list字段的取值是固定的（比如你示例中的cars、trucks等），直接使用静态PIVOT即可，这是性能最优的方式：

SELECT 
  date,
  IFNULL(cars, 'NA') AS cars,
  IFNULL(trucks, 'NA') AS trucks,
  IFNULL(vans, 'NA') AS vans,
  IFNULL(trailers, 'NA') AS trailers
FROM (
  SELECT date, list, value
  FROM `table`
)
PIVOT(
  SUM(value)
  FOR list IN ('cars', 'trucks', 'vans', 'trailers')
)

注：如果数据库默认空值显示为NULL，可以用IFNULL或COALESCE将其替换为NA；如果不需要替换，直接用SELECT *即可。

动态PIVOT实现（类别不固定时） #

如果list字段的取值可能新增或变化，不想每次手动修改SQL，可以用动态SQL自动获取所有类别并完成转置（以BigQuery为例）：

DECLARE list_categories STRING;

-- 自动获取所有唯一的类别并拼接成PIVOT需要的格式
SET list_categories = (
  SELECT STRING_AGG(DISTINCT CONCAT("'", list, "'"), ', ')
  FROM `table`
);

-- 构造并执行动态PIVOT语句
EXECUTE IMMEDIATE FORMAT("""
  SELECT * FROM (
    SELECT date, list, value
    FROM `table`
  )
  PIVOT(
    SUM(value)
    FOR list IN (%s)
  )
""", list_categories);

这种方式会自动适配list字段的所有取值，无需手动维护列名。

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