Link to home
Start Free TrialLog in
Avatar of thamilto0410
thamilto0410

asked on

How to group a pivot by month and year?

Good Day I have the following statement:

select * from (select team, pages, month from
   (select team, pages, nvl(to_char(date_stamp, 'MM-YYYY'),0) month from theTable t1, theothertable t2 where t1.osuser=t2.las and t1.code_status='A' and t1.team in ('team1','team2','team3','team4','team5','team6','team7','team8')
    and trunc(t2.date_stamp) between   to_date('" & start_date.Text.ToString & "', 'MM/DD/YYYY')  and   to_date('" & end_date.Text.ToString & "', 'MM/DD/YYYY') and t2.active is not null order by substr(team, 1,1)))
     PIVOT(sum(pages) for month IN ('01' as ""January"",'02' as ""February"",'03' as ""March"",'04' as ""April"",'05' as ""May"",'06' as ""June"", '07' as ""July"", '08' as ""August"", '09' as ""September"", '10' as ""October"", '11' as ""November"", '12' as ""December""))     order by substr(team, 1, 1),team
   
The above works but groups by calendar month in calendar month order ie: (Jan Feb Mar Apr etc).  I need to group by year and then month as our fiscal year does not go like calendar year and then teams needs to be in alpha order.  I need like the below

 TEAM      November 2012   December 2012  January 2013
Team1           150         250                350           500  
Team2            75         100                122           222

AND NOT LIKE

TEAM      JANUARY    NOVEMBER  DECEMBER  
Team1         500             150               350
Team2         222               75               122

I had to use pivot as the date field is one of the columns in each row and needed to be the column as above.  I don't mind rewriting this so as to group by calendar year and then by month I just don't know how to.  Someone please provide an example of how to do this with a pivot and if that is not possible how can I rewrite the query without pivot but turning the rows into columns as the date_stamp is one of the columns in each row and looks like "10/1/2012".

I know what group by and order by are I just don't know how to apply it to the above to get grouped by calendar year then month and then teams in alpha order.   Thanks.
Avatar of paquicuba
paquicuba
Flag of United States of America image

This is all I can think of...

Run the following SQL and you'll see what I'm talking about:


with theTable as
(
select
  decode(mod(rownum,2),0,'team1','team2') team,
  1 pages,
  to_char(add_months(sysdate,rownum-1),'MM')||LPAD(DENSE_RANK() OVER(ORDER BY to_char(add_months(sysdate,rownum-1),'RR')),2,'0') MONTH
from dual
connect by level <= 12
)
SELECT *
FROM
  (SELECT team,
    pages,
    MONTH
  FROM
    (SELECT team,
      pages,
      MONTH
    FROM theTable t1
    ORDER BY TEAM
    )
  ) PIVOT(SUM(pages) FOR MONTH IN (
                                  '0101' as "January current year",
                                  '0201' as "February current year",
                                  '0301' as "March current year",
                                  '0401' as "April current year",
                                  '0501' as "May current year",
                                  '0601' as "June current year",
                                  '0701' as "July current year",
                                  '0801' as "August current year",
                                  '0901' as "September current year",
                                  '1001' as "October current year",
                                  '1101' as "November current year",
                                  '1201' as "December current year",
                                  '0102' as "January next year",
                                  '0202' as "February next year",
                                  '0302' as "March next year",
                                  '0402' as "April next year",
                                  '0502' as "May next year",
                                  '0602' as "June next year",
                                  '0702' as "July next year",
                                  '0802' as "August next year",
                                  '0902' as "September next year",
                                  '1002' as "October next year",
                                  '1102' as "November next year",
                                  '1202' as "December next year"                                          
                                  )
  )  
ORDER BY team;
Avatar of thamilto0410
thamilto0410

ASKER

paquicuba:  i am attempting to run the above against my oracle database.  Where does my second table in my query factor in what you wrote? paquicuba:  Never mind I got the dual sql statement working and see what you mean.  I will give it some thought but...
Unfortunately the PIVOT clause doesn't allow the aliasing of the columns in an ordered and dynamic way
paquicuba: The sql is for a .net based application written in vb with an oracle back end.  Having said that is there any other way I can write the query for this one report so that if a user entered say: 10/1/2012   and 2/1/2013  in the start date field and the stop date field (dates are dynamic as they are input by user)I could group the above results (ie: this report sums the pages per team for a date range) like

                 OCT 2012   NOV 2012  DEC 2012 JAN 2013  FEB 2013
TEAM1          100                111            222         333         444

Is there another way to achieve without PIVOT and if so how?
ASKER CERTIFIED SOLUTION
Avatar of paquicuba
paquicuba
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The above got me to thinking and with some thought and modification on the above I was able to work up a solution to my problem.  Thanks much.
Glad you found a way to solve your problem!