sql-server - 使用回车换行符将多行合并为一列

Question

下面的代码(我从 here 获得)很好地将多个字段合并在一起，并用逗号分隔它们。

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

我需要用回车换行符替换逗号。我尝试了下面的代码，但它开始用这些字符分隔条目:“#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

我怀疑问题出在“FOR XML PATH('')),1,1,''”，但我不知道要输入什么值。

任何帮助将不胜感激。

谢谢!

Answer 1

您实际上可以替换 STUFF 结果上的逗号。

试试这个:

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