Mahipal Padigela's Blog

T-SQL String Concatenation using FOR XML PATH(”)

Sometimes we need to convert (concatenate) string column values for a given group into a
comma(or any character) separated list of values.

eg:
I have this:

I want this:

here is one way of doing this in T-SQL using FOR XML PATH(”) (only works with SQL Server 2005 and above)

select NoteID, STUFF(
	(SELECT  ',' + a.Comments  AS [text()]
	from Notes  a
	where a.NoteID = b.NoteID
	Order by a.Comments
	for xml PATH('')),1,1,''	) AS Comments_Concatenated
 from Notes b
group by NoteID
ORDER BY NoteID

or convert all rows into csv..

SELECT 'MP' NAME
into #tt1
UNION
SELECT 'BP' NAME
UNION
SELECT 'HP' NAME

select name from #tt1

declare @strTemp as varchar(100)

Select @strTemp = STUFF(
	(SELECT  ',' + a.Name  AS [text()]
	from #tt1  a
	for xml PATH('')),1,1,'' )  

print @strTemp

STUFF function removes the leading comma

Tags: FOR XML PATH(''), String Concatenation, STUFF