Creating a long string from a result set

Question

I have a result set in MS-SQL within a stored procedure, and lets say it has one VARCHAR column, but many rows. I want to create a comma separated string conataining all these values, Is there an easy way of doing this, or am I going to have to step through each result and build the string up manually?

Preferably I'd like to do this in the Stored Procedure itself.

Answer 1

Here is one way (using AdventureWorks2008 DB):

DECLARE @name varchar(255)
SET @name = NULL

select @Name = COALESCE(@Name + ',','') + LastName from Person.Person
Select @name

And here is another (for SQL 2005 onwards):

SELECT 
    LastName + ','
FROM 
    Person.Person
FOR XML PATH('')

In both cases you will need to remove the trailing comma ',' (can use STUFF() function)

Answer 2

This question has been asked a few times before:

http://stackoverflow.com/questions/122942/how-to-return-multiple-values-in-one-column-t-sql