transpose multiple rows into columns

Hi

I am having table(xpid,emailid) with data as below
xpid      emailid
======================
dbg\xxx      xxx@gmail.com
dbg\yyy      yyy@gmail.com
dbg\zzz      zzz@gmail.com

i want oput put as below:

emailid
========
xxx@gmail.com;yyy@gmail.com;zzz@gmail.com
knaren1975Asked:
Who is Participating?
 
David ToddSenior DBACommented:
Hi,

WHY?

if it is to simplify sending an email or two, I suggest that you don't. The reason is that you risk have all the emails bounce and none get delivered if _one_ fails.

One of the early things I did in my current role was the reverse - loop through the delimited string and send each email individually.

see
http://www.sqlservercentral.com/scripts/Miscellaneous/31894/ 

for a solution to your question. In your case, replace the coma with a semicolon.

HTH
  David
0
 
blandyukCommented:
Here is a CURSOR you can use to do it:

DECLARE curEmails CURSOR FOR
	SELECT Email FROM [tbl_Table];

DECLARE @List varchar(max), @Email varchar(256);

SET @List = '';

OPEN curEmails
FETCH NEXT FROM curEmails INTO @Email

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @List = @List + @Email + ';'
	FETCH NEXT FROM curEmails INTO @Email
END

CLOSE curEmails
DEALLOCATE curEmails

SELECT @List;

Open in new window

0
 
cyberkiwiCommented:
As David says, don't be too smart.  Also, there is a limit (spam control) to how many addressees you can have per email.

In any case, the sql statement is

select stuff((select ';' + emailid
    from tbl
    for xml path ('a'), type).value('.','nvarchar(max)'),1,1,'') as emailist;

or if you had other columns to group by

select account, stuff((select ';' + b.emailid
    from tbl b
    where b.account = a.account
    for xml path ('a'), type).value('.','nvarchar(max)'),1,1,'') as emailist
from tbl a
group by account
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.