sql server programming: I need to write a stored proc against a table to return a single string
containing a concatenation of all email addresses, separated with
semi-colons.

ID Email
1 sam@hotmail.com
2 dave@msn.com
3 julie@yahoo.com

Result = sam@hotmail.com;dave@msn.com;julie@yahoo.com

I'm assuming I need to use a cursor. Any quick sample or example would be
appreciated.

Thanks,
Ron

Ronald S. Cook, MCSD, MCT
Sr. Systems Consultant
Westin Information Services, Inc.
http://www.westinis.com

Ron,

[quoted text, click to view]


You can do this with a simple loop.

declare @count int
declare @result table (
ProductName varchar(40),
List varchar(7995)
)
insert @result
select min(ProductName), min(ProductName) from Products
select @count = @@rowcount

while (@count > 0) begin
update result set
ProductName = Products.ProductName,
List = result.List + ';' + Products.ProductName
from @result result, Products
where Products.ProductName = (
select min(ProductName) from Products
where Products.ProductName > result.ProductName)
select @count = @@rowcount
end
select List from @result


Or you can use bcp and bulk insert to do the concatenation for you.


create table #File (Body varchar(8000))
declare @List varchar(8000)

exec master..xp_cmdshell 'bcp "select ProductName from
Northwind..Products order by ProductName" queryout
C:\Products.txt -c -t -r; -T -Slindaw\ddbt'

bulk insert #File from 'C:\Products.txt'
with (fieldterminator = '', rowterminator = '\0')

set @List = (select top 1 left(Body, len(Body)-1) from #File)
select @List List

drop table #File


Both of these methods assume that the output string will not exceed
8000 characters.
They will also work if the number of items to concatenate is
unknown.

Linda

Or:

Thanks to David Portas for the DDL.

DECLARE @string varchar(1000)
SELECT @string = COALESCE(@string + '; ' + email, email, '') FROM Contacts
SELECT @string


Results:

dave@msn.com; julie@yahoo.com; sam@hotmail.com


[quoted text, click to view]