mssql group by id other column comma separated Edit

Given a table with an Id and FullName column, we want to group the column by Id and the result should be Id and coma separated FullNames. Given below is the input table with two columns GroupId & FullName.

GroupId	FullName
1	Murugan
1	Anand
2	Manish
1	Jacob

GroupId		FullNames
1		Murugan, Anand, Jacob
2		Manish

Solution

Please find below the code to create a temporary table #Test and insert 4 rows.

drop table IF EXISTS #Test
create Table #Test
(
	GroupId		int	NULL,
	FullName	nvarchar(64) NULL
)

insert into #Test(GroupId,FullName) values(1, 'Murugan'),(1, 'Anand'),(2, 'Manish'),(1, 'Jacob')

Here is the query to get comma separated value with group by Id

SELECT GroupId, FullNames = STUFF((SELECT ', '   CONVERT(varchar(10), FullName)
FROM #Test t1
WHERE t1.GroupId = t2.GroupId
FOR XML PATH('')), 1, 2, '')
FROM #Test t2
GROUP BY GroupId