asked on

SQL Server Pivot Query

I have an email table with 3 columns
CampaignID
emailaddress
typeresponse

the typeresponse has 5 possible types (open, bounce, deferred, click, unsubscribe)

I need a select that groups by CampaignID and emailaddress
And then place a 1 or 0 in the 5 pivoted columns if that record exists

As in
CampaignID emailaddress open bounce deferred click unsubscribe
12345 test@live.com 1 0 0 1 0

Guy Hengel [angelIII / a3]

select campaignid
,email
,max(case when typeresponse ='open' tben 1 else 0 end ) [open]
.... same for the other typeresponsed...
from yourtable
group by campaignid
, email

Nakul Vachhrajani

You can use the PIVOT operator:

DECLARE @campaignTable TABLE (CampaignID INT            NOT NULL,
                              EmailAddress VARCHAR(255) NOT NULL,
                              TypeResponse VARCHAR(25)  NOT NULL
                             );

INSERT INTO @campaignTable (CampaignID, EmailAddress, TypeResponse)
VALUES (123, 'openresponse@123.com',         'Open'),
       (123, 'bouncedresponse@123.com',      'Open'),
       (123, 'bouncedresponse@123.com',      'Bounce'),
       (123, 'clickresponse@123.com',        'Open'),
       (123, 'clickresponse@123.com',        'Click'),
       (234, 'donotdisturbresponse@123.com', 'Open'),
       (234, 'donotdisturbresponse@123.com', 'Unsubcribe'),
       (345, 'bouncedresponse@123.com',      'Open'),
       (345, 'bouncedresponse@123.com',      'Bounce'),
       (345, 'deferredresponse@123.com',     'Deferred'),
       (345, 'clickresponse@123.com',        'Open'),
       (345, 'clickresponse@123.com',        'Click');


SELECT PivotedTable.CampaignID,
       PivotedTable.EmailAddress,
       PivotedTable.[Open],
       PivotedTable.[Bounce],
       PivotedTable.[Deferred],
       PivotedTable.[Click],
       PivotedTable.[Unsubscribe]
FROM (SELECT ct.CampaignID,
             ct.EmailAddress,
             ct.TypeResponse
      FROM @campaignTable AS ct
     ) AS SourceTable
PIVOT (COUNT(SourceTable.TypeResponse) FOR TypeResponse IN ([Open], 
                                                            [Bounce], 
                                                            [Deferred], 
                                                            [Click], 
                                                            [Unsubscribe]
                                                           )
      ) AS PivotedTable
ORDER BY PivotedTable.CampaignID,
         PivotedTable.EmailAddress

Open in new window

Larry Brister

ASKER

Perfect... as always thanks.
Quick follow-up
This may be run on 50k records at a time.
Should that be a concern if the indexes are "optimized?"

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Larry Brister

ASKER

angelll,
As always... thanks for your help.

You cave me one of my first solutions back in 2004 I believe.