changing loop

hi,

i have a table #emails containing one coloumn called email. this is a list the contains emails.

i need to run a query that creats emails list like the following:

a@.xxx.com;b@.yyy.com, etc'

I am using the following code:

while exists(select 1 from #emails)
begin
select top 1 @.email=email
from #emails

set @.emails = @.emails + @.email + ';'

delete from #emails
where email=@.email

end

the thing is that when I create this list I get a high CPU usage.

Can someone help by suggesting a different way of creating this list and by that acheiving 'normal' CPU usage?

thx,

tomer

possible alternatives

--SQL SERVER 2005
DECLARE @.str varchar(4000)
SET @.str = (SELECT d.name + ','
FROM HumanResources.Department d
ORDER BY d.name
FOR XML PATH(''))
SET @.str = SUBSTRING(@.str,1,LEN(@.str)-1)
SELECT @.str

--SQL SERVER 2000
DECLARE @.name varchar(50)
DECLARE @.str varchar(4000)
SET @.str = ''
DECLARE Build CURSOR
LOCAL
FORWARD_ONLY
FOR
SELECT d.name
FROM HumanResources.Department d
ORDER BY d.name
OPEN Build
FETCH NEXT FROM Build INTO @.name
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.str = @.str + @.name + ','
FETCH NEXT FROM Build INTO @.name
END
CLOSE Build
DEALLOCATE Build
SET @.str = SUBSTRING(@.str,1,LEN(@.str)-1)
SELECT @.str

|||

...forgot this...eternity is a long time

DECLARE @.count int

SET @.count = (SELECT COUNT(*) FROM #emails)

while @.count > 0

begin

select top 1 @.email=email

from #emails

set @.emails = @.emails + @.email + ';'

delete from #emails

where email=@.email

SET @.count = @.count - 1

end