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ரதீப்
  • 88,697
  • 17
  • 124
  • 160
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
  • 71,300
  • 6
  • 42
  • 62
  • 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