Merge rows

I've been tearing my hair out for the past few days trying to get a merge across rows in SQL SERVER 200. Here is an example result set that I have so far:

Code Snippet

zzzz_X@.X_orange.net 0 0 1
zzzz_X@.X_orange.net 0 1 0
zzzz_X@.X_orange.net 1 0 0
zztoproadking_X@.X_yahoo.ca 0 0 1
zztoproadking_X@.X_yahoo.ca 0 1 0
zztoproadking_X@.X_yahoo.ca 1 0 0
zztoponly_X@.X_yahoo.com 0 0 1
zztoponly_X@.X_yahoo.com 0 1 0
zztoponly_X@.X_yahoo.com 1 0 0
zzjozz_X@.X_aol.com 0 0 1

What I want to do ultimately is merge those repeated email addresses into a single row, such as:

Code Snippet

zzzz_X@.X_orange.net 1 1 1
zztoproadking_X@.X_yahoo.ca 1 1 1
zztoponly_X@.X_yahoo.com 1 1 1
zzjozz_X@.X_aol.com 0 0 1

I realize this is possible using cursors, but is there a simple SQL way to achieve the same results?

Here's my SQL for the first result set:

Code Snippet

SELECT DISTINCT TOP 10 C.EmailAddress,
CASE C.CampaignID WHEN '1' THEN '1' ELSE '0' END AS travel_deals,
CASE C.CampaignID WHEN '2' THEN '1' ELSE '0' END AS contests,
CASE C.CampaignID WHEN '3' THEN '1' ELSE '0' END AS picks_of_the_week
FROM dbo.MarketingEmailCampaignAddresses C
LEFT OUTER JOIN Members M
ON C.EmailAddress = M.email
GROUP BY C.EmailAddress, C.campaignID, M.memberID
ORDER BY C.EmailAddress DESC

You can group them using "group by" clause.

SELECT TOP 10

C.EmailAddress,
max(CASE WHEN C.CampaignID = '1' THEN '1' ELSE '0' END) AS travel_deals,
max(CASE WHEN C.CampaignID = '2' THEN '1' ELSE '0' END) AS contests,
max(CASE WHEN C.CampaignID = '3' THEN '1' ELSE '0' END) AS picks_of_the_week

FROM

dbo.MarketingEmailCampaignAddresses C
LEFT OUTER JOIN Members M
ON C.EmailAddress = M.email

GROUP BY

C.EmailAddress

ORDER BY C.EmailAddress DESC

go

AMB

|||What is the MAX() doing in this case? The columns after the email address are booleans (or bit in SQL SERVER dialect).

While your way does seem to do an email address merge, I'm getting 1,1,1 for every email address.
|||

You are in the rite track, but missed the group by function which group all your row wise data into single row. You can use max/sum as per your requirement.

This is the legacy approach to get the Pivot data(swaping row based values into Columns),

Code Snippet

SELECT DISTINCT TOP 10

C.EmailAddress,

C.campaignID,

M.memberID,

Isnull(Max(CASE C.CampaignID WHEN '1' THEN '1' END),0) AS travel_deals,

Isnull(Max(CASE C.CampaignID WHEN '2' THEN '1' END),0) AS contests,

Isnull(Max(CASE C.CampaignID WHEN '3' THEN '1' END),0) AS picks_of_the_week

FROM

dbo.MarketingEmailCampaignAddresses C

LEFT OUTER JOIN Members M

ON C.EmailAddress = M.email

GROUP BY

C.EmailAddress,

C.campaignID,

M.memberID

ORDER BY

C.EmailAddress DESC

|||

I really don’t know how you got all 1.

Here the sample,

Create Table #marketingemailcampaignaddresses (

[EmailAddress] Varchar(30) ,

[CampaignID] int

);

Insert Into #marketingemailcampaignaddresses Values('zzzz_X@.X_orange.net','1');

Insert Into #marketingemailcampaignaddresses Values('zzzz_X@.X_orange.net','2');

Insert Into #marketingemailcampaignaddresses Values('zztoproadking_X@.X_yahoo.ca','3');

Insert Into #marketingemailcampaignaddresses Values('zztoponly_X@.X_yahoo.com','1');

Insert Into #marketingemailcampaignaddresses Values('zztoponly_X@.X_yahoo.com','2');

Insert Into #marketingemailcampaignaddresses Values('zztoponly_X@.X_yahoo.com','3');

Insert Into #marketingemailcampaignaddresses Values('zzjozz_X@.X_aol.com','2');

Select

EmailAddress,

Case When CampaignID = '1' Then 1 Else 0 End AS travel_deals,

CASE WHEN CampaignID = '2' THEN 1 ELSE 0 ENDAS contests,

CASE WHEN CampaignID = '3' THEN 1 ELSE 0 ENDAS picks_of_the_week

From

#marketingemailcampaignaddresses

/*

EmailAddresstravel_deals contestspicks_of_the_week

-- --

zzzz_X@.X_orange.net100

zzzz_X@.X_orange.net010

zztoproadking_X@.X_yahoo.ca001

zztoponly_X@.X_yahoo.com100

zztoponly_X@.X_yahoo.com010

zztoponly_X@.X_yahoo.com001

zzjozz_X@.X_aol.com010

*/

Select

EmailAddress,

Isnull(Max(Case When CampaignID = '1' Then 1 End),0) AS travel_deals,

Isnull(Max(CASE WHEN CampaignID = '2' THEN 1 END),0)AS contests,

Isnull(Max(CASE WHEN CampaignID = '3' THEN 1 END),0)AS picks_of_the_week

From

#marketingemailcampaignaddresses

Group By

EmailAddress

/*

EmailAddresstravel_deals contestspicks_of_the_week

-- --

zzjozz_X@.X_aol.com010

zztoponly_X@.X_yahoo.com111

zztoproadking_X@.X_yahoo.ca001

zzzz_X@.X_orange.net110

*/

|||You are absolutely correct. I was getting all 1's when trying to run the pivot without creating a temp table with the original results.

I had to do an SELECT INTO and create a temp table, then run a SELECT from that table to achieve the correct results.

Thanks for the help (both of you)!