5

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.

Kevin Fairchild
  • 10,891
  • 6
  • 33
  • 52
Sekhat
  • 4,435
  • 6
  • 43
  • 50

1 Answers1

7

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)

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541