SQL Server how to select collapse row?

Question

I want to write a SQL statement to include the same data row:

Index            name       column 
----------  ----------  --------
1           test1       1
1           test1       2
1           test1       3
2           test2       2
2           test2       3
3           test3       4
3           test3       5
3           test3       6

Output

Index            name       column 
----------  ----------  --------
1           test1       1,2,3
2           test2       2,3
3           test3       4,5,6

How do I write such a SQL statement?

Answer 1

Try this

SELECT TI.*, 
       (SELECT [Column] + ',' 
        FROM   [Table_Name] C 
        WHERE  C.[Index] = TI.[Index] 
               AND C.[NAME] = TI.[NAME] 
        FOR xml path('')) AS [COLUMN] 
FROM   (SELECT DISTINCT [Index], 
                        [NAME] 
        FROM   [Table_Name]) TI