Solved

SQL Server Pivot Query

Posted on 2016-07-21
5
48 Views
Last Modified: 2016-07-21
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
0
Comment
Question by:lrbrister
  • 2
  • 2
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41723083
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
0
 
LVL 12

Expert Comment

by:Nakul Vachhrajani
ID: 41723092
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


Results of PIVOT operator
1
 

Author Comment

by:lrbrister
ID: 41723103
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?"
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 41723105
50k records is not big.
and unless a clustered index on either campaignid or email a index is not needed or helpful
0
 

Author Closing Comment

by:lrbrister
ID: 41723109
angelll,
As always... thanks for your help.

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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question