SQL Server 2019中如何对多分隔Names字段值按字母排序后合并

问题背景 #

现有数据表names，其中Names字段为分号分隔的多值格式：

ID  Names
1   Apple;Zebra;Boy
2   Dog;Balloon;Mall
3   Phone;TV;Computer;Lamp

需将Names中的值按字母顺序排序后重新合并，最终得到如下结果：

ID  Names
 1  Apple;Boy;Zebra
 2  Balloon;Dog;Mall
 3  Computer;Lamp;Phone;TV

使用环境为SQL Server 2019（v15.0.2000），尝试拆分存入临时表再用STUFF合并，但合并结果未按序排列。

原尝试代码问题分析 #

• 临时表的ORDER BY仅控制查询结果的显示顺序，不保证表内数据存储有序，后续查询临时表时若未指定排序，数据顺序是不确定的。
• STUFF子查询中未指定拆分后项的排序逻辑，导致合并时采用默认存储顺序，无法得到有序结果。

最优解决方案（SQL Server 2017+） #

利用SQL Server 2017引入的STRING_AGG聚合函数，直接完成拆分、排序、合并操作，无需临时表或自定义函数：

SELECT 
    ID,
    STRING_AGG(LTRIM(RTRIM(s.Item)), ';') WITHIN GROUP (ORDER BY LTRIM(RTRIM(s.Item)) ASC) AS Sorted_Names
FROM names
CROSS APPLY STRING_SPLIT(Names, ';', 1) s
GROUP BY ID;

说明：STRING_SPLIT的第三个参数1（SQL Server 2019+支持）用于返回原始拆分顺序，但此处我们按字母排序，所以即使不指定该参数也可正常工作；LTRIM(RTRIM())用于处理可能存在的首尾空格。

兼容STUFF的修改方案 #

若坚持使用STUFF+XML的方式，需在子查询内部对拆分后的项进行排序：

-- 保留你定义的SplitStrings函数
SELECT 
    DISTINCT one.ID,
    STUFF((
        SELECT ';' + LTRIM(RTRIM(two.Item))
        FROM names s
        CROSS APPLY dbo.SplitStrings(s.Names, ';') two
        WHERE s.ID = one.ID
        ORDER BY LTRIM(RTRIM(two.Item)) ASC  -- 关键：在此指定排序规则
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS Sorted_Names
FROM names one;

说明：必须在生成XML的子查询中加入ORDER BY，才能确保合并后的字符串是按字母排序的。

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