如何对含可变列的BigQuery表进行动态透视转置？

要实现你需要的动态透视效果，核心是用窗口函数标记行号 + 动态SQL生成透视列——因为BigQuery的静态PIVOT无法自动适配列数，具体实现如下：

步骤1：为每个客户的匹配项添加序号 #

先给每个Cid分组内的Match记录按顺序标记行号，后续用这个行号生成Match_n列：

WITH numbered_data AS (
  SELECT
    Cid,
    Match,
    -- 按Match排序标记行号，可根据需求调整排序字段
    ROW_NUMBER() OVER(PARTITION BY Cid ORDER BY Match) AS row_num
  FROM
    `你的项目.你的数据集.你的表名`
),
max_col_calc AS (
  -- 获取所有客户中最多的匹配项数量，确定最终列数
  SELECT MAX(row_num) AS max_cols FROM numbered_data
)

步骤2：动态生成并执行透视SQL #

通过EXECUTE IMMEDIATE拼接动态列名，自动生成Match_1到Match_{max_cols}列：

EXECUTE IMMEDIATE (
  SELECT CONCAT(
    'SELECT * FROM (',
    '  SELECT Cid, Match, row_num FROM numbered_data',
    ') PIVOT (',
    '  ANY_VALUE(Match) AS Match FOR row_num IN (',
    -- 生成1到max_cols的列号列表
    STRING_AGG(CONCAT('`', CAST(num AS STRING), '`'), ', ' ORDER BY num),
    ')',
    ')'
  )
  FROM UNNEST(GENERATE_ARRAY(1, (SELECT max_cols FROM max_col_calc))) AS num
);

关键说明： #

• ANY_VALUE(Match)：由于每个Cid+row_num组合唯一，用ANY_VALUE/MAX/MIN均可，结果一致。
• 动态列生成：通过GENERATE_ARRAY生成对应数量的行号，再用STRING_AGG拼接成透视所需的列列表。
• 最终效果：自动生成对应数量的Match_n列，无匹配项的位置显示NULL，完全符合你的需求。

用你提供的测试数据执行后，结果如下：

（注：你给出的示例中A的Match_2值为1应为笔误，实际按数据生成是2）

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