如何获得列值逗号seperatedly包括ID

Question

我在我的当前表有

id  value 
1  newyork 
1  boston 
1  dallas 

我需要下面的输出

id  value 
1  newyork, boston, dallas 

Answer 1

Declare @YourTable table(ID int,[value] varchar(50)) 
Insert Into @YourTable values 
(1,'newyork'), 
(1,'boston'), 
(1,'dallas') 

Select A.ID 
     ,Value = (Select Stuff((Select Distinct ',' +value From @YourTable Where ID=A.ID For XML Path ('')),1,1,'')) 
From (Select Distinct ID From @YourTable) A 

返回要求

ID Value 
1 boston,dallas,newyork