Using Pivot to create report - SQL 2005

I have 2 tables: MailFile and Responder.
Mailfile has the following columns: Country, CouponCode.
Responder has the following columns: CouponCode, Active, Inactive.

Need a report that shows the following:

Country     Qty Mailed  Active  Inactive    Non-Responder
Greece      100         25      50          25
Malaysia    50          10      30          10
Singapore   200         100     75          25

Qty Mailed will be a count where country = Greece for 1st row, Malaysia for 2nd row and Singapore for 3rd row from mailfile table.

Active and Inactive will be a count of each in the responder table where couponcode matches in both tables.

Non-responder will be where there is NO Match in the 2 tables. Did not know whether a PIVOT command would work in SQL or not.

more ▼

asked May 07, 2010 at 11:43 PM in Default

avatar image

Tom Redd
57 4 6 6

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I am not 100% sure I understand your question but here is an attempt at a solution (it does not use PIVOT and has some assumptions about your column types)... I believe you are looking for each country, along with a total number of codes, total number of active codes, total number of inactive codes, and total number of unknown codes that you are referring too as non-responders. Note that this code was tested on SQL 2008. I do not see any reason why it would not be backwards compatible with SQL 2005.

--- Clear temp objects IF OBJECT_ID('tempdb..#MailFile') IS NOT NULL DROP TABLE #MailFile

IF OBJECT_ID('tempdb..#Responder') IS NOT NULL DROP TABLE #Responder

--- Table / Data setup CREATE TABLE #MailFile ( Country varchar(32), CouponCode int )

CREATE TABLE #Responder ( CouponCode int, Active bit, Inactive bit )

INSERT INTO #MailFile ( Country, CouponCode ) SELECT 'Greece', 1 UNION ALL SELECT 'Greece', 2 UNION ALL SELECT 'Greece', 3 UNION ALL SELECT 'Greece', 4 UNION ALL SELECT 'Greece', 5 UNION ALL SELECT 'Malaysia', 1 UNION ALL SELECT 'Malaysia', 3 UNION ALL SELECT 'Malaysia', 4 UNION ALL SELECT 'Singapore', 1 UNION ALL SELECT 'Singapore', 4 UNION ALL SELECT 'Singapore', 5

INSERT INTO #Responder ( CouponCode, Active, Inactive ) SELECT 1, 1, 0 UNION ALL SELECT 2, 0, 1 UNION ALL SELECT 3, 1, 0 UNION ALL SELECT 4, 0, 1

--- Return each country, along with appropriate counts (total, active, inactive, unknown) SELECT MF.Country, QtyMailed = COUNT(MF.CouponCode), Active = COUNT(R1.Active), Inactive = COUNT(R2.Inactive), NonResponder = COUNT(MF.CouponCode) - COUNT(R1.Active) - COUNT(R2.Inactive) FROM #MailFile MF LEFT JOIN #Responder R1 ON MF.CouponCode = R1.CouponCode AND R1.Active = 1 LEFT JOIN #Responder R2 ON MF.CouponCode = R2.CouponCode AND R2.Inactive = 1 GROUP BY MF.Country

more ▼

answered May 08, 2010 at 01:29 AM

avatar image

John Franklin
414 1 3 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 07, 2010 at 11:43 PM

Seen: 1527 times

Last Updated: May 08, 2010 at 05:44 AM

Copyright 2017 Redgate Software. Privacy Policy