0

I have a requirement where my current table has

id     value
1      newyork
1      boston
1      dallas

I need the following output

id      value
1       newyork, boston, dallas
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
sqlvan
  • 37
  • 4

1 Answers1

2
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

Returns

ID  Value
1   boston,dallas,newyork
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
  • Thanks for your help but there is a small issue I lost the order in my result, how can I keep the order same as in table? appreciate all your efforts. – sqlvan Dec 30 '16 at 16:50
  • @sqlvan There is no inherent row order unless you have another key . If you remove the DISTINCT they will come in order of the table variable, but you run the risk of dupes – John Cappelletti Dec 30 '16 at 17:08