Query for tranposed 'date' data

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

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

do you have to transpose horizontally?
here is a start:

PHP Code:

---

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'); 


---

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.

PHP Code:

---

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''))',
     
   p_anchor => my_pkg.array( 'DATA_TYPE_SID' ),
   p_pivot  => my_pkg.array( 'TOTAL' ),
   p_cursor => :x );
end;
/

PL/SQL procedure successfully completed.


DATA_TYPE_SID FEB-2004_1 JUN-2002_2 APR-2004_3 JUN-2002_4 AUG-2002_5 FEB-2003_6 APR-2003_7 AUG-2003_8 DEC-2003_9 FEB-2004_10 MAR-2004_11 APR-2004_12 MAY-2004_13 JUL-2004_14 JUL-2003_15
------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
            1          2
            2                    12          4
            8                                          25          3          1          1          1          1          20           3           2           1           5
           10                                                                                                                                                                          1

Elapsed: 00:00:00.00 


---

Thanks Cookies