BigQuery多行转多列：大量ID场景下的最优实现方案咨询

原始数据表 #

预期输出表 #

当前通过多次左自连接实现转置，但需要处理150个ID时，这种方式会导致查询效率极低，甚至可能超出BigQuery的资源限制。以下是两种更优的实现方案：

方案1：使用PIVOT操作（静态列场景） #

BigQuery原生支持PIVOT语法，可直接将行转列为指定字段，相比多次连接性能提升显著。

SELECT *
FROM (
  SELECT CustomerID, Year, ID, value
  FROM `your-project.your-dataset.your-table`
  WHERE CustomerID = 1000 -- 指定目标CustomerID
)
PIVOT (
  MAX(value) FOR ID IN (1477 AS ID_1477, 1474 AS ID_1474, 1464 AS ID_1464)
)
ORDER BY Year DESC;

• 说明：MAX(value)是PIVOT要求的聚合函数，由于每个(CustomerID, Year, ID)组合唯一，用MAX/MIN/ANY_VALUE均可
• 如需处理150个ID，只需在IN子句中依次补充ID AS ID_XXX格式的字段即可，无需重复写连接逻辑

方案2：动态生成PIVOT列（动态列场景） #

如果ID数量不固定或需要自动适配所有ID，可通过BigQuery脚本动态生成转置语句：

DECLARE pivot_columns STRING;

-- 自动收集指定CustomerID下的所有ID，拼接成PIVOT列格式
SET pivot_columns = (
  SELECT STRING_AGG(DISTINCT CONCAT(CAST(ID AS STRING), ' AS ID_', CAST(ID AS STRING)), ', ')
  FROM `your-project.your-dataset.your-table`
  WHERE CustomerID = 1000
);

-- 执行动态生成的查询
EXECUTE IMMEDIATE FORMAT("""
  SELECT *
  FROM (
    SELECT CustomerID, Year, ID, value
    FROM `your-project.your-dataset.your-table`
    WHERE CustomerID = 1000
  )
  PIVOT (
    MAX(value) FOR ID IN (%s)
  )
  ORDER BY Year DESC
""", pivot_columns);

• 说明：该脚本会自动识别目标CustomerID下的所有ID，无需手动维护150个ID的列表，适配性更强

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