SQL Server Convert Particular Column Values into comma separated String

Question

I have a table in Database as below :

Id  Name
1   Item1
1   Item2
2   Item3
1   Item4
3   Item5

I need output as below(3rd column is count):

1   Item1,Item2,Item4   3
2   Item3               1
3   Item5               1

How it can achieved by SQL Query ?

Answer 1

SQL Server has STUFF() function which could able to help you.

SELECT t.Id,
       Name = STUFF( (SELECT DISTINCT ','+Name 
                      FROM table 
                      WHERE Id = t.Id 
                      FOR XML PATH('')
                     ), 1, 1, ''
                   ) 
FROM table t
GROUP BY t.Id;