Query for tranposed 'date' data
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Query for tranposed 'date' data

Hybrid View

  1. #1
    Join Date
    Mar 2004
    Posts
    55

    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

  2. #2
    Join Date
    Oct 2002
    Posts
    182
    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'); 
    Last edited by Cookies; 09-02-2004 at 11:37 AM.
    - Cookies

  3. #3
    Join Date
    Oct 2002
    Posts
    182
    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 pivotp_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 pivotp_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 => :);
    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 
    - Cookies

  4. #4
    Join Date
    Mar 2004
    Posts
    55
    Thanks Cookies

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width