Cross-Tab Report - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Cross-Tab Report

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-03 : 09:45:09

ky writes "I have a table described as below

ProductName     DateIn      Qty
-----------     ------      ---
AAA             02/05/2002  56
BBB             31/07/2002  36
CCC             01/08/2002  96
DDD             29/09/2002  100
CCC             05/08/2002  20
EEE             06/09/2003  30

How this table1 can shown as below

ProductName    Jan Feb Mar Apr May Jun Jul Aug Oct Sep Nov Dec
-----------    --- --- --- --- --- --- --- --- --- --- --- ---
AAA            0   0   0   0   56  0   0   0   0   0   0   0
BBB            0   0   0   0   0   0   36  0   0   0   0   0
CCC            0   0   0   0   0   0   0   116 0   0   0   0
DDD            0   0   0   0   0   0   0   0   96  0   0   0

with consideration of the parameter sent 'Year'.

How all this can be pictured in one single Store Procedure ?

How to take into account that only lunar year has 29 days for Feb?"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-04-03 : 10:02:14

3 minor corrections to your expected results....

1.....sequence across should read "aug sep oct...."
2.....DDD has input data of 100...where does 96 come from?
3.....EEE missing in result set.

re solution....you will need to (left) join a master list of valid months....to your current input data

(tableofmonths below)
ie record 1, jan
record 2, feb
record 3, mar....etc...

ie...select b.month, sum(qty) from masterlistofmonths a
left join currentinputtable b
on b.month = getmonthpartof(a.recorddate)
group by b.month

then cross-tab this result set...should get you close to your required output.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-04-03 : 11:30:56

Someone correct me if I'm wrong, but doesn't using a view to do the pivot then just selecting from the view in the stored proc make the query execute faster?

maybe a better answer for you:


create view ProductsPivot as
 select ProductName,
        year(DateIn) as Year
        sum(case Month(DateIn) = 1 then Qty else 0 end) as Jan,
        sum(case Month(DateIn) = 2 then Qty else 0 end) as Feb,
        sum(case Month(DateIn) = 3 then Qty else 0 end) as Mar,
        sum(case Month(DateIn) = 4 then Qty else 0 end) as Apr,
        sum(case Month(DateIn) = 5 then Qty else 0 end) as May,
        sum(case Month(DateIn) = 6 then Qty else 0 end) as Jun,
        sum(case Month(DateIn) = 7 then Qty else 0 end) as Jul,
        sum(case Month(DateIn) = 8 then Qty else 0 end) as Aug,
        sum(case Month(DateIn) = 9 then Qty else 0 end) as Sep,
        sum(case Month(DateIn) = 10 then Qty else 0 end) as Oct,
        sum(case Month(DateIn) = 11 then Qty else 0 end) as Nov,
        sum(case Month(DateIn) = 12 then Qty else 0 end) as Dec
   from Products
  group by ProductName,
           year(DateIn)
go

create procedure ProductsByYear @year smallint as
  set nocount on

  select ProductName,
         Jan,
         Feb,
         Mar,
         Apr,
         May,
         Jun,
         Jul,
         Aug,
         Sep,
         Oct,
         Nov,
         Dec
    from ProductsPivot
   where Year = @year
   order by ProductName
go

Edited by - onamuji on 04/03/2002 11:39:22

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-03 : 11:46:56

That's a great way to do it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources