Getting comma separated string for particular field in sqlserver.

In this example I am showing how to get comma separated string for particular filed in sqlserver. I am using sqlserevr 2008.

Table Data:

Id   Email                     Name
2    TEST@TEST.COM    A
2    TEST@TEST.COM    B
2    TEST@TEST.COM    C
2    TEST@TEST.COM    D
2    TEST@TEST.COM    E
1    TEST@TEST.COM    A
1    TEST@TEST.COM    C
1    TEST@TEST.COM    D
1    TEST@TEST.COM    B
1    TEST@TEST.COM    D
5    TEST@TEST.COM    A
5    TEST@TEST.COM    C
5    TEST@TEST.COM    D
5    TEST@TEST.COM    B
5    TEST@TEST.COM    D

Output:

1    A,B,C,D,D
2    A,B,C,D,E
5    A,B,C,D,D

DECLARE @TABLE TABLE(ID INT, EMAIL VARCHAR(20), NAME VARCHAR(10))

INSERT INTO @TABLE
SELECT 2    ,’TEST@TEST.COM’,    ‘A’ UNION ALL
SELECT 2    ,’TEST@TEST.COM’,    ‘B’ UNION ALL
SELECT 2    ,’TEST@TEST.COM’,    ‘C’ UNION ALL
SELECT 2    ,’TEST@TEST.COM’,    ‘D’ UNION ALL
SELECT 2    ,’TEST@TEST.COM’,    ‘E’ UNION ALL
SELECT 1    ,’TEST@TEST.COM’,    ‘A’ UNION ALL
SELECT 1    ,’TEST@TEST.COM’,    ‘C’ UNION ALL
SELECT 1    ,’TEST@TEST.COM’,    ‘D’ UNION ALL
SELECT 1    ,’TEST@TEST.COM’,    ‘B’ UNION ALL
SELECT 1    ,’TEST@TEST.COM’,    ‘D’ UNION ALL
SELECT 5    ,’TEST@TEST.COM’,    ‘A’ UNION ALL
SELECT 5    ,’TEST@TEST.COM’,    ‘C’ UNION ALL
SELECT 5    ,’TEST@TEST.COM’,    ‘D’ UNION ALL
SELECT 5    ,’TEST@TEST.COM’,    ‘B’ UNION ALL
SELECT 5    ,’TEST@TEST.COM’,    ‘D’

SELECT T2.ID,
STUFF((SELECT ‘,’+T1.NAME FROM @TABLE T1 WHERE T1.ID=T2.ID ORDER BY T1.NAME FOR XML PATH(”)),1,1,”)
FROM @TABLE T2
GROUP BY T2.ID