I am trying to display some data so that its grouped by month and the month is tranposed horizontally. What i have looks like something like this
select company, count(decode(TO_CHAR(reportdate,'MON-YYYY'),'JAN-2004',1))"JAN-2004",count(decode(TO_CHAR(reportdate,'MON-YYYY'),'FEB-2004',1))"FEB-2004",count(decode(TO_CHAR(reportdate,'MON-YYYY'),'MAR-2004',1))"MAR-2004",count(decode(TO_CHAR(reportdate,'MON-YYYY'),'APR-2004',1))"APR-2004",count(decode(TO_CHAR(reportdate,'MON-YYYY'),'MAY-2004',1))"MAY-2004",count(decode(TO_CHAR(reportdate,'MON-YYYY'),'JUN-2004',1))"JUN-2004",count(decode(TO_CHAR(reportdate,'MON-YYYY'),'JUL-2004',1))"JUL-2004",count(decode(TO_CHAR(reportdate,'MON-YYYY'),'SEP-2004',1))"SP-2004",count(decode(TO_CHAR(reportdate,'MON-YYYY'),'OCT-2004',1))"OCT-2004",count(decode(TO_CHAR(reportdate,'MON-YYYY'),'NOV-2004',1))"NOV-2004"
from workorder where reportdate<sysdate group by company
Now my problem is that I want to stop at the current month. I don't want to be adding N lines for N months in the future manually
How Can I rewrite this so it stops at the current month. Is there another approach?
Is this something I would need to look at a procedure?
Any grand ideas or where i can find some good tutorial on procedures.
Thanks
Cookies
09-02-2004, 12:35 PM
do you have to transpose horizontally?
here is a start:
select
company,
to_char(reportdate, 'MON-YYYY') month_year,
count(*) total
from workorder
where reportdate < sysdate
group by
company,
to_char(reportdate, 'MON-YYYY')
order by
to_date(to_char(reportdate, 'MON-YYYY'), 'MON-YYYY');
Cookies
09-02-2004, 03:23 PM
in PL/SQL you can automate this.
i took the liberty of editing one of Tom Kite's handy
general pivot packages. All you would really have to do is
use the query I supplied above and throw in the pertinent data
accordingly.
create or replace package my_pkg
as
type refcursor is ref cursor;
type array is table of varchar2(30);
procedure pivot( p_max_cols in number default NULL,
p_max_cols_query in varchar2 default NULL,
p_query in varchar2,
p_anchor in array,
p_pivot in array,
p_cursor in out refcursor );
end;
/
create or replace package body my_pkg
as
procedure pivot( p_max_cols in number default NULL,
p_max_cols_query in varchar2 default NULL,
p_query in varchar2,
p_anchor in array,
p_pivot in array,
p_cursor in out refcursor )
as
l_max_cols number;
l_query long;
l_cnames array;
title_query varchar2(500);
month_year varchar2(30);
begin
/* figure out the number of columns we must support */
/* we either KNOW this or we have a query that can tell us */
if ( p_max_cols is not null )
then
l_max_cols := p_max_cols;
elsif ( p_max_cols_query is not null )
then
execute immediate p_max_cols_query into l_max_cols;
else
raise_application_error(-20001, 'Cannot figure out max cols');
end if;
/* Now, construct the query that can answer the question for us */
/* start with the C1, C2, ... CX columns: */
l_query := 'select ';
for i in 1 .. p_anchor.count
loop
l_query := l_query || p_anchor(i) || ',';
end loop;
/* Now add in the C{x+1}... CN columns to be pivoted: */
/* the format is "max(decode(rn,1,C{X+1},null)) cx+1_1" */
for i in 1 .. l_max_cols
loop
for j in 1 .. p_pivot.count
loop
title_query := 'select month_year from ('||p_query||') where rn = '||i;
execute immediate title_query into month_year;
l_query := l_query ||
'max(decode(rn,'||i||','||
p_pivot(j)||',null)) "' ||
month_year || '",';
end loop;
end loop;
/* Now just add in the original query */
l_query := rtrim(l_query,',')||' from ( '||p_query||') group by ';
/* and then the group by columns... */
for i in 1 .. p_anchor.count
loop
l_query := l_query || p_anchor(i) || ',';
end loop;
l_query := rtrim(l_query,',');
/* and return it */
execute immediate 'alter session set cursor_sharing=force';
open p_cursor for l_query;
execute immediate 'alter session set cursor_sharing=exact';
end;
end;
/
/*--------------------------*/
/* now run this in sqlplus */
/*--------------------------*/
variable x refcursor
set autoprint on
begin
my_pkg.pivot
(p_max_cols_query => 'select max(rn) from (
select row_number() over (
order by data_type_sid,to_date(to_char(entered_datetime, ''MON-YYYY''), ''MON-YYYY'') ) rn
from tattribute where entered_datetime < sysdate
group by data_type_sid, to_char(entered_datetime, ''MON-YYYY''))',
p_query => 'select * from (select data_type_sid,
to_char(entered_datetime, ''MON-YYYY'') month_year,
count(*) total,
row_number() over (order by data_type_sid,to_date(to_char(entered_datetime, ''MON-YYYY''), ''MON-YYYY'') ) rn
from tattribute
where entered_datetime < sysdate
group by data_type_sid, to_char(entered_datetime, ''MON-YYYY'')
order by to_date(to_char(entered_datetime, ''MON-YYYY''), ''MON-YYYY''))',