SQL Query - Concatenating Results into One String

The Solution to SQL Query - Concatenating Results into One String is

If you're on SQL Server 2005 or up, you can use this FOR XML PATH & STUFF trick:

DECLARE @CodeNameString varchar(100)

   @CodeNameString = STUFF( (SELECT ',' + CodeName 
                             FROM dbo.AccountCodes 
                             ORDER BY Sort
                             FOR XML PATH('')), 
                            1, 1, '')

The FOR XML PATH('') basically concatenates your strings together into one, long XML result (something like ,code1,code2,code3 etc.) and the STUFF puts a "nothing" character at the first character, e.g. wipes out the "superfluous" first comma, to give you the result you're probably looking for.

UPDATE: OK - I understand the comments - if your text in the database table already contains characters like <, > or &, then my current solution will in fact encode those into &lt;, &gt;, and &amp;.

If you have a problem with that XML encoding - then yes, you must look at the solution proposed by @KM which works for those characters, too. One word of warning from me: this approach is a lot more resource and processing intensive - just so you know.

~ Answered on 2011-03-04 16:24:06

Most Viewed Questions: