CODEWITHSUNDEEP
sqlt-sqlstring
Sekhat
Sekhat

Reputation: 4489

Creating a long string from a result set

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.

Upvotes: 5

Views: 4851

Answers (1)

Mitch Wheat
Mitch Wheat

Reputation: 300827

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)

Upvotes: 7

Related Questions