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)!
