如何在BigQuery中根据条件转置/合并行？

输入表格 #

期望输出 #

SQL解决方案 #

支持窗口函数的数据库（PostgreSQL、MySQL 8.0+、SQL Server等） #

通过窗口函数生成分组ID，将连续的to_merge=true行归为同一组，再聚合提取非空值：

SELECT
  MIN(record) AS record,
  MAX(name1) AS name1,
  MAX(name2) AS name2,
  MAX(value1) AS value1,
  MAX(value2) AS value2
FROM (
  SELECT
    *,
    SUM(CASE WHEN to_merge = FALSE THEN 1 ELSE 0 END) OVER (ORDER BY record) AS group_id
  FROM your_table
) t
GROUP BY group_id
ORDER BY record;

旧版MySQL（无窗口函数） #

使用用户变量生成分组ID实现相同逻辑：

SELECT
  MIN(record) AS record,
  MAX(name1) AS name1,
  MAX(name2) AS name2,
  MAX(value1) AS value1,
  MAX(value2) AS value2
FROM (
  SELECT
    *,
    @group_id := @group_id + CASE WHEN to_merge = FALSE THEN 1 ELSE 0 END AS group_id
  FROM your_table, (SELECT @group_id := 0) init
  ORDER BY record
) t
GROUP BY group_id
ORDER BY record;

逻辑说明 #

1. 分组规则：每遇到to_merge=false的行，分组ID自动递增，确保所有连续的to_merge=true行被划分到同一个分组；
2. 聚合处理：用MAX()函数提取每个分组中各字段的非空值（MAX会自动忽略null），用MIN(record)保留分组中最小的record作为合并后行的标识；
3. 排序输出：按合并后的record排序，得到与期望一致的结果。

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