SQL Query - Concatenating Results into One String

sql, sql-function, sql-server, t-sql

Solution

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

DECLARE @CodeNameString varchar(100)

SELECT 
   @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.

Problem

I have a sql function that includes this code: ``` DECLARE @CodeNameString varchar(100) SELECT CodeName FROM AccountCodes ORDER BY Sort ``` I need to concatenate all results from the select query into CodeNameString. Obviously a FOREACH loop in C# code would do this, but how do I do it in SQL?

Original source

Related problems