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!

Answer 1

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