• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 98
  • Last Modified:

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
0
Larry Brister
Asked:
Larry Brister
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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
 
Larry Bristersr. DeveloperAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
50k records is not big.
and unless a clustered index on either campaignid or email a index is not needed or helpful
0
 
Larry Bristersr. DeveloperAuthor Commented:
angelll,
As always... thanks for your help.

You cave me one of my first solutions back in 2004 I believe.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now