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

Tom Redd gravatar image

Tom Redd
57 4 4 4

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

1 answer: sort newest

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

John Franklin gravatar image

John Franklin
414 1 1 3

You are the man. I edited the entry a little to use a couple of case statements b/c active and inactive were data entries in the responsetype column. Once I did this, it worked perfectly. Thank you!
May 08, 2010 at 11:01 AM Tom Redd
+1 for the very nicely formatted and easy to read commented code with test tables and data. Well done, John. Now, if we could just get the bloody OPs to do the same thing, eh? ;-)
May 14, 2010 at 07:56 PM Jeff Moden
Thanks Jeff... I started working with SQL Server at a company that has a good set of coding standards and best practices and it includes nicely formatted code. For debug work and research purposes having something that is half way readable is always a plus...
May 15, 2010 at 10:42 AM John Franklin
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 1074 times

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