SQL如何按相同MID将同表多行数据合并为单行多列

我有一张结构如下的数据表：

期望得到的输出结果如下：

需求：将表中MID相同的多行数据合并为单行展示，合并逻辑不受列中空值的影响。

之前尝试用STUFF结合FOR XML PATH的写法拼接字段，逻辑过于复杂且结果不符合预期，部分代码如下：

select [MID],
    STUFF(
        (select concat('', [FromCountry]) 
        FROM test i
        where i.[MID] = o.[MID]
        for xml path ('')),1,1,'') as FromCountry
    ,stuff (
        (select concat('', [FromState]) 
        FROM test i
        where i.[MID] = o.[MID]
        for xml path ('')),1,1,'') as FromState
    ,stuff (
        (select concat('', [FromCity]) 
        FROM test i
        where i.[MID] = o.[MID]
        for xml path ('')),1,1,'') as FromCity
    ,stuff (
        (select concat('', [FromAddress]) 
        FROM test i
        where i.[MID] = o.[MID]
        for xml path ('')),1,1,'') as FromAddress
FROM test o
group by [MID] 
...

求可行的实现方法。

你之前用STUFF+FOR XML PATH的写法本质是字符串聚合，会把同MID的多个字段值拼成一个长字符串，和你要的「同MID多行数据拆成多列单行展示」的行转列需求完全不匹配，自然得不到正确结果。
从样例数据看每个MID固定对应2行记录，用窗口函数打行号后做关联查询即可实现，逻辑简单，也完全不会受空值影响。

实现步骤 #

• 先通过窗口函数给每个MID分组内的行编序号，从1开始计数
• 筛选序号为1的行作为基础数据，左关联同MID下序号为2的行，把第二行的所有字段加后缀1作为新列输出即可

参考SQL代码 #

WITH RankedData AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY MID ORDER BY (SELECT 1)) AS RowSeq
    FROM test
)
SELECT
    t1.MID,
    -- 第一行原始字段
    t1.FromCountry,
    t1.FromState,
    t1.FromCity,
    t1.FromAddress,
    t1.FromNumber,
    t1.FromApartment,
    t1.ToCountry,
    t1.ToCity,
    t1.ToAddress,
    t1.ToNumber,
    t1.ToApartment,
    -- 第二行带1后缀的新字段
    t2.FromCountry AS FromCountry1,
    t2.FromState AS FromState1,
    t2.FromCity AS FromCity1,
    t2.FromAddress AS FromAddress1,
    t2.FromNumber AS FromNumber1,
    t2.FromApartment AS FromApartment1,
    t2.ToCountry AS ToCountry1,
    t2.ToCity AS ToCity1,
    t2.ToAddress AS ToAddress1,
    t2.ToNumber AS ToNumber1,
    t2.ToApartment AS ToApartment1
FROM RankedData t1
LEFT JOIN RankedData t2 
    ON t1.MID = t2.MID AND t2.RowSeq = 2
WHERE t1.RowSeq = 1

说明：如果需要固定同MID下行的排列顺序，把窗口函数里的ORDER BY (SELECT 1)替换成你需要的排序字段（比如FromNumber、FromCountry）即可；如果后续同一个MID对应超过2行数据，只需要继续左关联对应行号的别名表，就能扩展出更多列。

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