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
No comments yet.
Leave a comment
You must be logged in to post a comment.