Merge multiple rows into one column using carriage return line feeds

Question

The code below (which I got from here ) does a good job at merging together multiple fields separating them with commas.

select player,
  stuff((SELECT distinct ', ' + cast(score as varchar(10))
       FROM yourtable t2
       where t2.player = t1.player
       FOR XML PATH('')),1,1,'') 
from yourtable t1
group by player

I need to replace the commas with carriage return line feeds. I tried the code below but it started separating the entries with these characters: "#x0D;"

select player,
  stuff((SELECT distinct CHAR(13)+CHAR(10) + cast(score as varchar(10))
       FROM yourtable t2
       where t2.player = t1.player
       FOR XML PATH('')),1,1,'') 
from yourtable t1
group by player

I suspect the problem is with the "FOR XML PATH('')),1,1,''" but I don't know what values to put.

Any help would be greatly appreciated.

Thanks!

score 11 · Accepted Answer · answered Mar 22 '13 at 14:22

11

You can actually replace the comma on the result of STUFF.

Try this:

select player,
  replace(stuff((SELECT distinct ', ' + cast(score as varchar(10))
       FROM yourtable t2
       where t2.player = t1.player
       FOR XML PATH('')),1,1,''), ',', char(13) + char(10))
from yourtable t1
group by player

answered Mar 22 '13 at 14:22

jerjer

8,694
30
36

how about going new line instead of comma? – Elbert John Felipe Mar 08 '18 at 10:38

score 4 · Answer 2 · edited Nov 13 '18 at 23:28

You can handle all the special characters that XML adds (of which #x0D; is one) by using:

FOR XML PATH(''), TYPE).value('.','nvarchar(max)')

More information here.

You will also need to adjust the third parameter of STUFF to the number of characters you need to skip at the start of your result (in this case to 2 because you have char(13) and char(10)).

So your solution would be:

select player,
  stuff((SELECT distinct CHAR(13)+CHAR(10) + cast(score as varchar(10))
       FROM yourtable t2
       where t2.player = t1.player
       FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,'') 
from yourtable t1
group by player

Merge multiple rows into one column using carriage return line feeds

2 Answers2