Re: SQL statement for report...

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Danny (istdrs_at_flash.net)
Date: 11/18/04 

Date: Thu, 18 Nov 2004 00:36:02 GMT

Aahh the classic pivot scenario. Basically add 12 case statements. I dummied it up a little due to the data but you get the idea. Also with the distinct you may want to distinct before the group with a derived table.

SELECT LTRIM(RTRIM(a.Item)) AS Item,
               (case month when 1 then Sum(a.Qty) else 0 end) as JanQty,
               (case month when 2 then Sum(a.Qty) else 0 end) as FebQty,
               (case month when 3 then Sum(a.Qty) else 0 end) as MarQty,
               (case month when 4 then Sum(a.Qty) else 0 end) as AprQty,
               (case month when 5 then Sum(a.Qty) else 0 end) as MayQty,
               (case month when 6 then Sum(a.Qty) else 0 end) as JunQty
FROM (select distinct * from Sales_View) a
GROUP BY LTRIM(RTRIM(a.Item)), month
--HAVING (CustID)= @CustID )
ORDER BY LTRIM(RTRIM(a.Item)), month

Danny

PS. SQL 2005 has a new pivot capability that will make this better.
  "The Eeediot" <eeediot@hotmail.com> wrote in message news:5qudnfqBSMH_UAbcRVn-iA@giganews.com...
  Hello, SQL gurus!

  This message use HTML tables (in case you see a bunch of gobbledy-gook).

  I need to modify an SQL statement:

  SELECT DISTINCT Trim(InvtID)+', '+Trim(Descr) AS Item, month, Sum(QtyShip) AS Qty
  FROM Sales_View
  GROUP BY Trim(InvtID)+', '+Trim(Descr), month, CustID
  HAVING (CustID)= @CustID )
  ORDER BY Trim(InvtID)+', '+Trim(Descr), month;

  Whose output is something like the following:

        Item
       month
       Qty
       
        26479F, Report Cover Sheets (100/pkg)
       3
       2
       
        26479F, Report Cover Sheets (100/pkg)
       8
       1
       
        26479F, Report Cover Sheets (100/pkg)
       9
       1
       
        46217C, Manual- 5th Ed
       6
       1
       
        46217C, Manual- 5th Ed
       8
       1
       
        514560, Profile Report
       8
       1
       
        51900C, Technical Manual
       1
       1
       
        ADHAC0, Adults and Children (kit)
       8
       6
       
        ADS04, Technical Manual 2nd Ed
       7
       15
       

  And turn it into something like this:

        Item
       JanQty
       FebQty
       MarQty
       AprQty
       MayQty
       JunQty
       JulQty
       AugQty
       SepQty
       OctQty
       NovQty
       DecQty
       
        26479F, Report Cover Sheets (100/pkg)
       0
       0
       2
       0
       0
       0
       0
       1
       1
       0
       0
       0
       
        46217C, Manual- 5th Ed
       0
       0
       0
       0
       0
       1
       0
       1
       0
       0
       0
       0
       
        514560, Profile Report
       0
       0
       0
       0
       0
       0
       0
       1
       0
       0
       0
       0
       
        51900C, Technical Manual
       1
       0
       0
       0
       0
       0
       0
       0
       0
       0
       0
       0
       
        ADHAC0, Adults and Children (kit)
       0
       0
       0
       0
       0
       0
       0
       6
       0
       0
       0
       0
       
        ADS04, Technical Manual 2nd Ed
       0
       0
       0
       0
       0
       0
       15
       0
       0
       0
       0
       0
       

  Can anyone help?!

  TIA...

Quantcast