时间戳

首页 > 解决方案 > 基于另一个列值合并列值 - TSQL

sql - 基于另一个列值合并列值 - TSQL

问题描述

我正在使用下面的查询来合并Message基于表中“Customer_Name”列的列Customers

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;了合并列似乎是错误的。

关于如何解决它的任何想法将不胜感激。

使用@Larnu 帮助回答并在评论中发帖

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]

标签： sqlsql-serverxmltsql

解决方案

防止 XML 实体化的正确方法xml实际上是使用选项将其生成为数据类型TYPE，然后使用.value

DECLARE @sep NVARCHAR(10) = ', ';  -- or you can use CHAR(13)+CHAR(10)
SELECT
  [Customer_Name],
  STUFF(
    (SELECT @sep + LTRIM(RTRIM([Message]))
     FROM [dbo].[Customers] t2
     WHERE t2.[Customer_Name] = t1.[Customer_Name]
     FOR XML PATH (''), TYPE
    ).value('text()[1]','nvarchar(max)'), 1, LEN(@sep), '')
FROM [dbo].[Customers] t1
GROUP BY [Customer_Name]

推荐阅读