TSQL如何按ID合并同组记录到单行 用回车分隔排序后的字段值

你现有代码不符合预期的核心原因有两个：

• 未指定聚合拼接时组内记录的排序规则：最外层的ORDER BY bi.TimeAtSrvOrderNum仅作用于分组后员工行的排序，无法控制每个员工名下多条公交记录的拼接顺序，且分组后直接引用非聚合字段TimeAtSrvOrderNum本身就不符合SQL语法规范
• 换行符不完整：仅使用CHAR(13)（回车符）无法保证SSMS文本模式下稳定识别换行，Windows环境下标准换行需要CHAR(13)+CHAR(10)（回车+换行双字符）

优先使用STRING_AGG方案（SQL Server 2017及以上版本支持） #

STRING_AGG性能远优于传统STUFF+FOR XML PATH方案，写法更简洁，也不会出现特殊字符XML转义的问题，不需要更换为STUFF方案，补全WITHIN GROUP排序子句、修正换行符即可满足需求，代码如下：

SELECT 
    bi.EmployeeID,
    STRING_AGG(bi.TimeOfDay + ' ' + bi.BusNumber, CHAR(13) + CHAR(10)) 
        WITHIN GROUP (ORDER BY bi.TimeAtSrvOrderNum) AS BusInformation
FROM #BusInfo AS bi
GROUP BY bi.EmployeeID;

代码说明：

• WITHIN GROUP (ORDER BY bi.TimeAtSrvOrderNum)专门用于指定聚合拼接时组内值的排列顺序，严格按照TimeAtSrvOrderNum字段值排序拼接
• 分隔符使用CHAR(13) + CHAR(10)后，在SSMS选择「结果以文本显示」时，每条时段+公交编号的组合会单独占一行，完全匹配预期效果

低版本兼容方案（SQL Server 2016及更早版本，无STRING_AGG支持） #

如果你的数据库版本不支持STRING_AGG，再使用STUFF+FOR XML PATH的传统写法：

SELECT 
    t1.EmployeeID,
    STUFF(
        (
            SELECT CHAR(13) + CHAR(10) + bi.TimeOfDay + ' ' + bi.BusNumber
            FROM #BusInfo AS bi
            WHERE bi.EmployeeID = t1.EmployeeID
            ORDER BY bi.TimeAtSrvOrderNum
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'),
        1, 2, ''
    ) AS BusInformation
FROM #BusInfo AS t1
GROUP BY t1.EmployeeID;

常见问题排查 #

如果执行后SSMS文本模式仍未按行显示，打开SSMS菜单：工具-选项-查询结果-SQL Server-以文本格式显示的结果，确认已勾选「保留CR/LF」选项，重启查询窗口后生效。

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