Solved

SQL Server Pivot Query

Posted on 2016-07-21
5
69 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 143

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 13

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 143

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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

717 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