Jira数据导入BigQuery后，如何动态合并相似列成单列？

核心思路 #

利用BigQuery的动态SQL结合INFORMATION_SCHEMA，自动识别所有匹配命名规则的环境影响列，无需手动维护列名列表，新增标签列会自动被纳入合并范围。

具体实现 #

1. 动态生成合并逻辑

通过INFORMATION_SCHEMA.COLUMNS筛选出所有以Custom_field__Environment_s__affected__开头的列，然后用STRING_AGG将这些列名拼接成数组表达式，最后用ARRAY_TO_STRING过滤空值并合并为逗号分隔的字符串。

执行以下动态SQL即可直接得到目标结果：

DECLARE column_list STRING;

-- 筛选所有符合命名规则的环境影响列
SET column_list = (
  SELECT STRING_AGG(
    CONCAT('`', column_name, '`'), 
    ', ' ORDER BY CAST(REGEXP_EXTRACT(column_name, r'__(\d+)$') AS INT64)
  )
  FROM `your_project.your_dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'your_jira_table'
    AND column_name LIKE 'Custom_field__Environment_s__affected__%'
);

-- 动态执行查询，合并列并输出结果
EXECUTE IMMEDIATE FORMAT("""
  SELECT 
    ticket_name,
    ARRAY_TO_STRING(
      [ %s ], 
      ', ' 
      IGNORE NULLS
    ) AS environments_affected
  FROM `your_project.your_dataset.your_jira_table`
""", column_list);

2. 关键细节说明

• REGEXP_EXTRACT(column_name, r'__(\d+)$')用来提取列名末尾的序号，确保合并时列的顺序和原数据一致。
• IGNORE NULLS参数会自动忽略空值列，避免合并结果出现多余的逗号。
• 替换代码中的your_project、your_dataset和your_jira_table为你实际的项目、数据集和表名。

效果 #

后续新增类似命名的环境标签列（比如Custom_field__Environment_s__affected__5），无需修改SQL，执行上述代码时会自动包含新列并合并。

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