如何在BigQuery中动态行转列，合并issues与issue_fields表？

静态列方案（字段名固定时使用） #

如果issue_fields表中的field_name是固定已知的，直接用CASE语句行转列后关联主表：

CREATE OR REPLACE TABLE `your_project.your_dataset.new_issues_table` AS
SELECT
  i.issue_id,
  i.created_date,
  MAX(CASE WHEN f.field_name = 'priority' THEN f.field_value END) AS priority,
  MAX(CASE WHEN f.field_name = 'status' THEN f.field_value END) AS status,
  MAX(CASE WHEN f.field_name = 'assignee' THEN f.field_value END) AS assignee
  -- 按实际field_name继续添加更多CASE语句
FROM `your_project.your_dataset.issues` i
LEFT JOIN `your_project.your_dataset.issue_fields` f
ON i.issue_id = f.issue_id
GROUP BY i.issue_id, i.created_date;

动态列方案（字段名不固定时使用） #

如果field_name会动态新增，需要自动识别所有字段并转列，用BigQuery的EXECUTE IMMEDIATE实现动态SQL：

DECLARE cols STRING;

-- 提取所有唯一field_name，拼接为行转列的CASE语句
SET cols = (
  SELECT STRING_AGG(
    CONCAT("MAX(CASE WHEN field_name = '", field_name, "' THEN field_value END) AS `", field_name, "`"),
    ", "
  )
  FROM (SELECT DISTINCT field_name FROM `your_project.your_dataset.issue_fields`)
);

-- 执行动态生成的建表SQL
EXECUTE IMMEDIATE CONCAT(
  "CREATE OR REPLACE TABLE `your_project.your_dataset.new_issues_table` AS ",
  "SELECT i.issue_id, i.created_date, ", cols, " ",
  "FROM `your_project.your_dataset.issues` i ",
  "LEFT JOIN `your_project.your_dataset.issue_fields` f ON i.issue_id = f.issue_id ",
  "GROUP BY i.issue_id, i.created_date"
);

关键说明 #

• 替换代码中的your_project.your_dataset为实际的项目和数据集路径
• 使用LEFT JOIN确保主表issues的所有记录都被保留，无对应字段的列值为NULL
• 动态方案会自动处理包含特殊字符的field_name（用反引号包裹列名）

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