SQL Query - Concatenating Results into One String

Question

This question already has an answer here:

• How to concatenate text from multiple rows into a single text string in SQL server? 44 answers

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?

Answer 1

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 <, >, and &.

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.

Answer 2

@AlexanderMP's answer is correct, but you can also consider handling nulls with coalesce:

declare @CodeNameString  nvarchar(max)
set @CodeNameString = null
SELECT @CodeNameString = Coalesce(@CodeNameString + ', ', '') + cast(CodeName as varchar) from AccountCodes  
select @CodeNameString

Answer 3

Here is another real life example that works fine at least with 2008 release (and later).

This is the original query which uses simple max() to get at least one of the values:

SELECT option_name, Field_M3_name, max(Option_value) AS "Option value", max(Sorting) AS "Sorted"
FROM Value_list group by Option_name, Field_M3_name
ORDER BY option_name, Field_M3_name

Improved version, where the main improvement is that we show all values comma separated:

SELECT from1.keys, from1.option_name, from1.Field_M3_name,

 Stuff((SELECT DISTINCT ', ' + [Option_value] FROM Value_list from2
  WHERE COALESCE(from2.Option_name,'') + '|' + COALESCE(from2.Field_M3_name,'') = from1.keys FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'') AS "Option values",

 Stuff((SELECT DISTINCT ', ' + CAST([Sorting] AS VARCHAR) FROM Value_list from2
  WHERE COALESCE(from2.Option_name,'') + '|' + COALESCE(from2.Field_M3_name,'') = from1.keys FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'') AS "Sorting"

FROM ((SELECT DISTINCT COALESCE(Option_name,'') + '|' + COALESCE(Field_M3_name,'') AS keys, Option_name, Field_M3_name FROM Value_list)
-- WHERE
) from1
ORDER BY keys

Note that we have solved all possible NULL case issues that I can think of and also we fixed an error that we got for numeric values (field Sorting).

Answer 4

For SQL Server 2005 and above use Coalesce for nulls and I am using Cast or Convert if there are numeric values -

declare @CodeNameString  nvarchar(max)
select  @CodeNameString = COALESCE(@CodeNameString + ',', '')  + Cast(CodeName as varchar) from AccountCodes  ORDER BY Sort
select  @CodeNameString

Answer 5

DECLARE @CodeNameString varchar(max)
SET @CodeNameString=''

SELECT @CodeNameString=@CodeNameString+CodeName FROM AccountCodes ORDER BY Sort
SELECT @CodeNameString

Answer 6

from msdn Do not use a variable in a SELECT statement to concatenate values (that is, to compute aggregate values). Unexpected query results may occur. This is because all expressions in the SELECT list (including assignments) are not guaranteed to be executed exactly once for each output row

The above seems to say that concatenation as done above is not valid as the assignment might be done more times than there are rows returned by the select