BigQuery中将列值转换为逗号分隔行值的实现方法

你在SQL Server中用来拼接动态透视列名的逻辑，在BigQuery中可以直接用内置聚合函数实现，不需要手动定义变量循环拼接。

等价实现代码 #

你原SQL Server代码的作用是：取出临时表中去重后的close_month值，为每个值添加标识符包裹，最终拼接为逗号分隔的字符串，作为透视查询的列名使用。BigQuery中对应的写法如下：

SELECT
  STRING_AGG(
    FORMAT('`%s`', close_month),
    ','
    ORDER BY close_month
  ) AS PivotColumnNames
FROM (
  SELECT DISTINCT close_month FROM `你的项目ID.你的数据集名.temp`
) AS close_months

逻辑对应说明 #

• 原SQL中手动处理空值、循环拼接逗号的逻辑，由BigQuery内置的STRING_AGG()函数直接实现，该函数专门用于按指定分隔符聚合并拼接多行字符串值，会自动处理分隔符拼接逻辑，不会出现首尾多余逗号的问题。
• 原SQL中QUOTENAME()的标识符包裹效果，在BigQuery中通过FORMAT('%s', close_month)实现：BigQuery的合法标识符引用符号为反引号，包裹后可以直接作为动态SQL的列名使用；如果不需要做标识符转义，直接传入close_month字段即可。
• ORDER BY close_month为可选配置，添加后可以固定拼接值的顺序，避免动态列顺序随机的问题。

效果示例 #

• 输入：去重后的close_month值为2023-01、2023-02、2023-03
• 输出：单个字符串 `2023-01`,`2023-02`,`2023-03`，和预期拼接结果完全一致。

如果需要实现完整的动态透视逻辑，可以结合EXECUTE IMMEDIATE直接执行拼接后的动态SQL，参考写法：

DECLARE pivot_columns STRING;
-- 拼接透视列名
SET pivot_columns = (
  SELECT STRING_AGG(FORMAT('`%s`', close_month), ',' ORDER BY close_month)
  FROM (SELECT DISTINCT close_month FROM `你的项目ID.你的数据集名.temp`)
);
-- 执行动态透视
EXECUTE IMMEDIATE FORMAT("""
  SELECT * FROM (
    SELECT dim_col, close_month, metric_col FROM `你的项目ID.你的数据集名.source_table`
  )
  PIVOT (
    SUM(metric_col) FOR close_month IN (%s)
  )
""", pivot_columns);

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