TSQL按Customer_Name合并Message列实现换行分隔的解决方法

问题背景 #

我使用如下查询语句，从Customers表按Customer_Name列分组，合并同分组下的Message列值：

SELECT
  [Customer_Name],
  STUFF((SELECT
    ', ' + LTRIM(RTRIM([Message]))
  FROM [dbo].[Customers] t2
  WHERE t2.[Customer_Name] = t1.[Customer_Name]
  FOR XML PATH ('')), 1, 1, '')
FROM [dbo].[Customers] t1
GROUP BY [Customer_Name]

上述代码合并后的Message值以逗号分隔，我需要改为换行分隔。尝试用CHAR(13)+CHAR(10)替换逗号后，返回结果出现#x0D;编码错误，合并结果异常。

修复方案 #

异常是因为默认的FOR XML PATH('')会自动转义特殊字符，回车符CHAR(13)会被转义为XML实体#x0D;，导致结果不符合预期。修复时需要增加TYPE参数指定返回XML类型，再通过value方法提取原始文本，即可保留换行符不被转义，同时注意换行符占2个字符，STUFF方法需要删除前2位的多余换行。
正确实现代码如下：

SELECT
  [Customer_Name],
  STUFF((SELECT
    (CHAR(13) + CHAR(10)) + LTRIM(RTRIM([Message]))
  FROM [Customers] t2
  WHERE t2.[Customer_Name] = t1.[Customer_Name]
  FOR XML PATH (''),TYPE
    ).value('(./text())[1]','varchar(MAX)'),1,2,'')
FROM [Customers] t1
GROUP BY [Customer_Name]

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