SQL中多行数据的重组与合并实现方案问询

原始数据表 #

目标结果表 #

当然可以用SELECT语句实现这个需求，核心逻辑是给每个ID下的非空Column B、Column C条目按顺序分配分组序号，再把同序号的B和C配对，同时关联对应ID的Column A值。

具体SQL代码 #

WITH b_groups AS (
    SELECT 
        ID,
        "Column B" AS column_b,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS rn
    FROM your_table
    WHERE "Column B" IS NOT NULL
),
c_groups AS (
    SELECT 
        ID,
        "Column C" AS column_c,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY (SELECT NULL)) AS rn
    FROM your_table
    WHERE "Column C" IS NOT NULL
),
a_values AS (
    SELECT 
        ID,
        "Column A" AS column_a
    FROM your_table
    WHERE "Column A" IS NOT NULL
)
SELECT 
    COALESCE(b.ID, c.ID) AS ID,
    a.column_a AS "Column A",
    b.column_b AS "Column B",
    c.column_c AS "Column C"
FROM b_groups b
FULL JOIN c_groups c ON b.ID = c.ID AND b.rn = c.rn
LEFT JOIN a_values a ON b.ID = a.ID
ORDER BY COALESCE(b.ID, c.ID), COALESCE(b.rn, c.rn);

关键说明 #

• 三个CTE分别处理：提取每个ID的非空Column A值，给非空Column B、Column C按ID分组并分配行号，行号用来确定B和C的配对关系；
• 用FULL JOIN保证不管B还是C的条目多，都能保留所有行；
• ORDER BY (SELECT NULL)是为了沿用原始数据的顺序，不同数据库可能有适配写法，比如MySQL可以用ORDER BY 1，如果有实际排序字段，替换成对应字段更稳妥。

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