Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite
4

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?

share|improve this question
1  
What version of SQL Server?? – marc_s Mar 4 '11 at 16:19
1  
answered many times... but watch out, not all FOR XML PATH concatenations implementations will properly handle the XML special characters (<, &, >, etc) like my answer to a previous answer will: stackoverflow.com/questions/5031204/… – KM. Mar 4 '11 at 19:26
add comment (requires an account with 50 reputation)

4 Answers

up vote 20 down vote accepted

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.

share|improve this answer
3  
this will NOT properly handle the XML special characters ( <, & , > , etc) like my previous answer will, see here: stackoverflow.com/questions/5031204/… – KM. Mar 4 '11 at 19:24
add comment (requires an account with 50 reputation)
up vote 7 down vote 
DECLARE @CodeNameString varchar(max)
SET @CodeNameString=''

SELECT @CodeNameString=@CodeNameString+CodeName FROM AccountCodes ORDER BY Sort
SELECT @CodeNameString
share|improve this answer
add comment (requires an account with 50 reputation)
up vote 4 down vote

@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
share|improve this answer
Yes, I forgot about coalesce/isnull. But wouldn't it make better sense to check CodeName instead of @CodeNameString? One could lose data this way. Better yet, instead of coaleste, it would make sense to use plain old Where CodeName is not null. – AlexanderMP Mar 4 '11 at 17:52
add comment (requires an account with 50 reputation)
up vote 2 down vote

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
share|improve this answer
100 is a bit small, don't you think;-) – James Wiseman Mar 4 '11 at 16:24
@James- Hmm..just copied that from OP's Question have updated it now..thanks...btw dang it you did beat me to it.. – Misnomer Mar 4 '11 at 16:28
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.