下面的代码(我从这里获取)将多个字段合并在一起,用逗号分隔,做得很好。
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,''",但我不知道要放什么值。
非常感谢任何帮助。
谢谢!