Merge column value based on another column value

I m using the below query to merge column Message based on column 'Customer_Name' from table Cusotomers

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]

Using the above code, the Message are separated by , but i want a new line. i try to use CHAR(13) CHAR(10) but i getting #x0D; and the merge column seems to be wrong.

Any idea on how to fix it will greatly appreciate.

Edited sample code to use CHAR(13) CHAR(10)

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]

CodePudding user response：

Your solution use xml serialization .

#x0D; is xml serialization of char(13)

If you use at least SQL Server 2017 you can use STRING_AGG function

SELECT
  [Customer_Name],
  STRING_AGG([Message],', ') as [Messages]
FROM [dbo].[Customers] t1
GROUP BY [Customer_Name]

otherwise you can add replace.

SELECT
  [Customer_Name],
  REPLACE(
  STUFF((SELECT
    ', '   LTRIM(RTRIM([Message])) 
  FROM [dbo].[Customers] t2
  WHERE t2.[Customer_Name] = t1.[Customer_Name]
  FOR XML PATH ('')), 1, 2, ''),
  '#x0D;',
  CHAR(13)) as [Messages]
FROM [dbo].[Customers] t1
GROUP BY [Customer_Name]

CodePudding user response：

The correct method to prevent XML entitization is actually to generate it as the xml data type using the TYPE option, then pull it back out with .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]