1

I have requirement where I need to convert list of rows into single column value.

Ex:

ID           Value
1            v1
1            v2
1            v3
2            v4
2            v5
2            v6
2            v7 
and so on..

Need to convert as

ID         New Value
1          v1,v2,v3
2          v4,v5,v6,v7

Please let me know how I can write query in SQL server.

Thanks.

Erran Morad
  • 4,563
  • 10
  • 43
  • 72
user2986108
  • 95
  • 1
  • 2
  • 7
  • http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 – Sam DeHaan Apr 03 '14 at 18:02

1 Answers1

5
SELECT DISTINCT ID, 
  STUFF((SELECT ',' + Value
         FROM Table T
         WHERE T.ID = ID
         FOR XML PATH ('')), 1, 1, '') AS [New Value]
FROM Table
GROUP BY ID

I got it from here: https://stackoverflow.com/a/1785923/215752

Community
  • 1
  • 1
Hogan
  • 69,564
  • 10
  • 76
  • 117
  • 6
    @BoratSagdiyev - If such was the case then I would have put a cast to a varchar in my example of course. Please point out more things that would be wrong if it was a different question. – Hogan Apr 03 '14 at 18:11