SQL Query - Concatenating Results into One String [duplicate]

Solution 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.

Solution 2:

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

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

Solution 3:

@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

Solution 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