如何在SQL PIVOT中无需硬编码值，自动包含所有buyer列取值？

首先明确：BigQuery的PIVOT语法不支持直接省略IN子句（比如PIVOT (SUM(cost) FOR buyer)），必须在IN里明确列出要转置的所有buyer取值。因为SQL是静态查询语言，查询执行前需要确定输出列的数量和名称，没办法在运行时动态推导。

但遇到buyer有大量取值（比如500个）的情况，不用手动逐一列出来，可以用动态SQL解决，步骤如下：

1. 先查询出所有distinct的buyer值，把它们格式化成IN子句需要的字符串格式（比如'Tom', 'Brady'）
2. 用EXECUTE IMMEDIATE执行动态生成的PIVOT语句

结合你的示例代码，完整的实现如下：

DECLARE buyer_values STRING;

-- 提取所有不重复的buyer值，拼接成IN子句需要的格式
SET buyer_values = (
  SELECT STRING_AGG(DISTINCT CONCAT("'", REPLACE(buyer, "'", "''"), "'"), ', ')
  FROM (
    SELECT * from UNNEST([
      STRUCT(1 as id,'Tom' as buyer, 108 as cost),
      (2,'Brady', 17),
      (3,'Tom', 19)
    ])
  )
);

-- 动态执行PIVOT查询
EXECUTE IMMEDIATE format("""
WITH purchases as (
  SELECT * from UNNEST([
    STRUCT(1 as id,'Tom' as buyer, 108 as cost),
    (2,'Brady', 17),
    (3,'Tom', 19)
  ])
)
SELECT * FROM (
  SELECT * EXCEPT(id) FROM purchases
)
PIVOT (SUM(cost) FOR buyer IN (%s))
""", buyer_values);

注意事项 #

• 代码里用REPLACE(buyer, "'", "''")是为了处理buyer值包含单引号的情况，避免SQL语法错误
• 动态SQL生成的列名就是buyer的原始取值，比如Tom、Brady，如果取值包含特殊字符，BigQuery会自动用反引号包裹（比如'Tom O\'Neil'会变成Tom O'Neil列，查询时需要用`Tom O'Neil`引用）

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