Merge column value based on another column value - TSQL

Question

I m using the below query to merge column Message based on column 'Customer_Name' from table 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]

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.

Answer using @Larnu help and posts on comments

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]

Does this answer your question? [String_agg for SQL Server before 2017](https://stackoverflow.com/q/49361088/2029983) (Specifically my answer that explains why this happens and how to fix it.) — Thom A, Oct 14 '21 at 09:13
@Larnu i try to follow you example but i dont manage to replace `,` with new line. My code works fine in regards of `,`. The request is to replace ',' with new line. Could you please provide any guidelines? — Error 1004, Oct 14 '21 at 09:36
If you want a new line then use`CHAR(13)+CHAR(10)`, instead of `','`, which is a carriage return followed by a line break and then ensure you get the `value` of the XML, like I show in the answer. — Thom A, Oct 14 '21 at 10:03
@Larnu i try to modify the query but now i getting one space before the first `Message` and 2 spaces before the new message. any advice? — Error 1004, Oct 14 '21 at 10:48
If there are "spaces" that would be in your data. But you have the `CHAR` functions the wrong way round, and you're only removing the first character with `STUFF`, not the first 2. — Thom A, Oct 14 '21 at 10:49
@Larnu modify the answer using `CHAR` function the right way, remove the first 2 characters from `STUFF` (see edited answer) but still no new line. — Error 1004, Oct 14 '21 at 11:04
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/238134/discussion-between-error-1004-and-larnu). — Error 1004, Oct 14 '21 at 11:13

Tohm · Answer 1 · 2021-10-14T09:32:05.340

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]

score 0 · Answer 2 · answered Oct 14 '21 at 13:21

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]

Merge column value based on another column value - TSQL

2 Answers2