mssql group by id other column comma separated edit button Edit

Murugan Andezuthu Dharmaratnam | calendar 12 March 2021 | 1516

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

And the expected output is given below.
GroupId		FullNames
1		Murugan, Anand, Jacob
2		Manish


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