SQL Query - Concatenating Results into One String [duplicate]

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.

DECLARE @CodeNameString varchar(max)SET @CodeNameString=''SELECT @CodeNameString=@CodeNameString+CodeName FROM AccountCodes ORDER BY SortSELECT @CodeNameString

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

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