SQLTeam.com | Weblogs | Forums

T-SQL Statemenet


#1

Hello,

I have got four tables.

PURCHASE
PurchaseID Date ProductID CustomerID
1 2012-02-01 1 1
2 2012-02-02 2 2
3 2012-02-03 3 3

PRODUCT
ProductID ProductName ProductPrice ProductTypeID
1 Michelin X4t 2000 150.00 1
2 Craftsman 6000T 97.00 2
3 Duracell AAA 6 Pack 12.00 3

PRODUCT TYPE
ProductTypeID ProductTypeName
1 Automobile Tires
2 Drills
3 Batteries
4 Motor Oil

CUSTOMER
CustomerID FirstName LastName PhoneNumber EmailAddress
1 Steve Smith 6045554567 ssmith@hotmail.com
2 John McLean 7675679876 johnm@gmail.com
3 Peter Harrington 6045762934 petedog@yahoo.ca

Using the sample data as a guide, how would you write a TSQL statement that lists the number of each product type purchased by each customer for the month of February 2012. Here is what the resulting data set would look like:

CustomerID FirstName LastName NumAutoTires NumDrills NumBatteries NumMotorOil
1 Steve Smith 4 0 10 1
2 John McLean 2 2 0 18
3 Peter Harrington 1 1 2 4
4 Wendy Foster 4 0 8 0


#2

Something like this:

WITH src
AS (
     SELECT c.CustomerID
          , c.FirstName
          , c.LastName
          , p.ProductID
          , p.Units
          , t.ProductTypeName
     FROM CUSTOMER c
     INNER JOIN (
          SELECT CustomerID
               , ProductID
               , count(*) AS Units
          FROM purchase p
          GROUP BY CustomerId
               , ProductId
          ) p
          ON c.CustomerID = p.CustomerID
     INNER JOIN PRODUCT pr
          ON p.ProductID = pr.ProductID
     INNER JOIN PRODUCT_TYPE t
          ON pr.ProductTypeID = t.ProductTypeID
     )
SELECT CustomerID
     , FirstName
     , LastName
     , isnull([Automobile Tires], 0) AS [Automobile Tires]
     , isnull([Drills], 0) AS Drills
     , isnull([Batteries], 0) AS Batteries
     , isnull([Motor Oil], 0) AS [Motor Oil]
FROM src
PIVOTsum(Units) FOR ProductTypeName IN ([Automobile Tires], [Drills], [Batteries], [Motor Oil])) pvt