0

i have a table in SQL 2008 as 

ID     Items
1      A
1      B
2      C
3      D
3      B

i would like to get the result as 

ID    Items
1     A,B
2     C
3     B,D

I have used cursors but it has considerably slowed the process , can i achieve the above result using group by query or through any other way.

Thanks and Regards

Kapil

APC
  • 144,005
  • 19
  • 170
  • 281
kapil Yadav
  • 1
  • 1
  • possible duplicate of [How do I Create a Comma-Separated List using a SQL Query?](http://stackoverflow.com/questions/1817985/how-do-i-create-a-comma-separated-list-using-a-sql-query/3087799#3087799) – Martin Smith Jul 12 '10 at 09:59

2 Answers2

2

You are looking for a way to concatenate the results. I don't think MSSQL has a function for that, but here's a very nice tutorial on how to create a method like that: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Wolph
  • 78,177
  • 11
  • 137
  • 148
0

I think 

GROUP_CONCAT

is the function you are looking for. Something like

SELECT id,
       GROUP_CONCAT (DISTINCT Items ORDER BY Items SEPARATOR ',')
    FROM my_table
    GROUP BY id;
Brian Hooper
  • 21,544
  • 24
  • 88
  • 139
  • `GROUP_CONCAT` is a MySQL command, and as far as I am aware, it is not available in SQL Server 2008. – Mike Jul 12 '10 at 09:56
  • True, no group_concat() in SQL Server 2008. WoLpH's answer seems to be the one to go with. – Nordic Mainframe Jul 12 '10 at 10:00
  • Oops. Sorry, chaps. Didn't notice that bit about SQL Server 2008. APC has thoughtfully corrected the tags. – Brian Hooper Jul 12 '10 at 10:05
  • Thanks everyone for your quick responses. WoLpH's comment was fruitfull. Like WoLpH i once again would like to direct users having this kind of issue to : http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ and check for the "cursor solution". Best Regards Kapil – kapil Yadav Jul 12 '10 at 11:16