如何使用sql server在一个raw中获取逗号分隔值？

I want to comma separated value in one row after some word comma separated value is a start in the table. Like this in 1 row UserA like category Cricket, Football. And in the 2 row UserB like category Reding, and, Wallyball. So now I want to this just category in one row like this Cricket, Football, Reading, Wallyball.

我希望逗号分隔值在一行中逗号分隔后的值是表中的一个开头。像这样在一排UserA喜欢类别Cricket，Football。并且在第2行UserB中类似Reding，以及Wallyball。所以现在我想把这个类别排在一排，比如这个板球，足球，雷丁，Wallyball。

This is my query =>

这是我的查询=>

select UserId,categorytext from Users

This is my current o/p =>

这是我目前的o / p =>

UserId  | categorytext
 1         UserA like category Cricket,Football  
 2         UserB like category Reding,and,Wallyball

I have tried this query =>

我试过这个查询=>

DECLARE @listStr VARCHAR(MAX)
SET @listStr = ''
SELECT @listStr = @listStr + categorytext + ','
FROM Users
SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1)

But using this query I am getting all the data in comma separated. I want to just after category word.

但是使用此查询我将以逗号分隔所有数据。我想在类别之后。

1 个解决方案

#1

Try this

尝试这个

select UserId,STUFF((SELECT DISTINCT ', '+categorytext from Users i 
WHERE  i.UserId=o.UserId FOR XML PATH ('')),1,2,'') AS categorytext
from Users o