Forum Stats

  • 3,853,198 Users
  • 2,264,190 Discussions
  • 7,905,285 Comments

Discussions

sql to generate yearly report

KODS
KODS Member Posts: 145
edited Jan 23, 2014 12:40PM in SQL & PL/SQL

Hi All,


I prepared a SQL to find out number of objects created by each user in the database in each month.

select * from (
  select owner,to_char(created,'MON') dt from dba_objects)
pivot(  count(1) for (dt) in ('JAN' as JAN,'FEB' as FEB,'MAR' as MAR,'APR' as APR,'MAY' as MAY,'JUN' as JUN,'JUL' as JUL,'AUG' as AUG,'SEP' as SEP,'OCT' as OCT,'NOV' as NOV,'DEC' as DEC))
order by owner;

SQL> r
  1  select * from (
  2    select owner,to_char(created,'MON') dt from dba_objects)
  3  pivot(  count(1) for (dt) in ('JAN' as JAN,'FEB' as FEB,'MAR' as MAR,'APR' as APR,'MAY' as MAY,'JUN' as JUN,'JUL' as JUL,'AUG' as AUG,'SEP' as SEP,'OCT' as OCT,'NOV' as NOV,'DEC' as DEC))
  4* order by owner

OWNER                            JAN   FEB   MAR   APR   MAY   JUN   JUL   AUG   SEP   OCT   NOV   DEC
------------------------------ ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
SCOTT                           1047   185     2     0     0    14     0  9869    55  1054     0   778
Praveen                          2434     0     0     0     0     1     0     0     0     0     0     0
JOHN                                 0     0     0     0     0     0     0     0     0     1     0     0


Now, I am trying to generate a report for yearly wise. I prepared the below queryb but it is not working. Please let me know where I am doing wrong.

select * from (
  select owner,to_char(created,'YYYY') dt from dba_objects)
pivot(  count(1) for (dt) <= to_char(sysdate,'YYYY'))
order by owner;


Thanks,
Kods

Tagged:
Sven W.
«1

Answers

  • Manik
    Manik Member Posts: 2,908 Gold Trophy
    edited Jan 22, 2014 1:32AM

    what is your expected output:

    ---

    Are you trying something like this?

    SELECT *
        FROM (SELECT owner, TO_CHAR (created, 'YYYY') dt FROM dba_objects) PIVOT (COUNT (
                                                                                     1)
                                                                           FOR (dt)
                                                                           IN  (2014,
                                                                               2013,
                                                                               2012,
                                                                               2011,
                                                                               2010))
    ORDER BY owner;
    

    Cheers,

    Manik.

  • KODS
    KODS Member Posts: 145

    Hi Manik,

    Thanks for reply. I am not interested to limited the query to specific years. My query need to be run for all the years present in database.

    I am looking for a generic query.

    Thanks,

    Kods

  • Partha Sarathy S
    Partha Sarathy S Member Posts: 1,200 Silver Trophy

    You can do it in generic using PIVOT_XML.

    select * from (
      select owner,to_char(created,'YYYY') dt from dba_objects)
    pivot xml(count(1) for (dt) in (ANY));
    
  • Partha Sarathy S
    Partha Sarathy S Member Posts: 1,200 Silver Trophy

    You will be knowing the year in which the database was created. Else you could get the minimum value of year from the query you have used. Then list the years from minimum value to current year in PIVOT clause and you can get it. Else another method could be to use MODEL clause.

    SELECT owner,
           dt,
           cnt_2009,
           cnt_2010,
           cnt_2011,
           cnt_2012,
           cnt_2013,
           cnt_2014
    FROM (
    select owner,
           to_char(created,'YYYY') dt,
           count(1) cnt from dba_objects
    group by owner,
             to_char(created,'YYYY'))
    MODEL RETURN UPDATED ROWS
      DIMENSION BY (owner,dt)
      MEASURES(cnt,0 cnt_2009,0 cnt_2010,0 cnt_2011,0 cnt_2012,0 cnt_2013,0 cnt_2014)
      RULES(
            cnt_2009[owner,2009]
                =cnt[cv(),cv()],
            cnt_2010[owner,2010]
                =cnt[cv(),cv()],            
            cnt_2011[owner,2011]
                =cnt[cv(),cv()],
            cnt_2012[owner,2012]
                =cnt[cv(),cv()],
            cnt_2013[owner,2013]
                =cnt[cv(),cv()],
            cnt_2014[owner,2014]
                =cnt[cv(),cv()]
            );
    
  • KODS
    KODS Member Posts: 145

    Hi Partha,

    Thanks for quick reply. I got the output as below. How do I display it ?

    SCOTT
    <PivotSet><item><column name = "DT">2012</column><column name = "COUNT(1)">10715

    Praveen
    <PivotSet><item><column name = "DT">2010</column><column name = "COUNT(1)">2435<

    JOHN
    <PivotSet><item><column name = "DT">2010</column><column name = "COUNT(1)">1</co

    Thanks

    Kods

  • Partha Sarathy S
    Partha Sarathy S Member Posts: 1,200 Silver Trophy

    You can use the method suggested in Oracle docs to extract data from XML. But it is not easy. Why don't you just mention the specific years to get your data using PIVOT or MODEL(the other method which I suggested). In general, row to column conversion would be requiring the values to be known in prior.

  • AnnEdmund
    AnnEdmund Member Posts: 1,466 Gold Trophy
    edited Jan 22, 2014 4:08AM

    You can try with below function. But that function generic for your case only. Try the below and let me know in case of any issues

    CREATE OR REPLACE FUNCTION dyn_row_colum (p_inyear DATE)
    RETURN SYS_REFCURSOR
    AS
     v_sqlstr VARCHAR2(32767) := 'SELECT owner,';
     v_rfcur  SYS_REFCURSOR;
    BEGIN
     FOR j_rec IN(SELECT EXTRACT(YEAR FROM created) dt 
                  FROM dba_objects
                  WHERE EXTRACT(YEAR FROM created) <= EXTRACT(YEAR FROM p_inyear)
                  GROUP BY EXTRACT(YEAR FROM created))
      LOOP
       v_sqlstr := v_sqlstr||' '||'COUNT(DECODE(EXTRACT(YEAR FROM created),'||''||j_rec.dt||''||',EXTRACT(YEAR FROM created)))'||' '||'"'||j_rec.dt||'"'||',';
      END LOOP;
       v_sqlstr := RTRIM(v_sqlstr,',')||' '||'FROM dba_objects GROUP BY owner ORDER BY 1';
       OPEN v_rfcur FOR v_sqlstr;
       RETURN v_rfcur;
    EXCEPTION
     WHEN OTHERS THEN
      RAISE;
    END dyn_row_colum;
    
    SELECT dyn_row_colum(SYSDATE) FROM dual; (OR)
    VAR rf_cur REFCURSOR; EXEC :rf_cur := dyn_row_colum('22-JAN-13');
  • AnnEdmund
    AnnEdmund Member Posts: 1,466 Gold Trophy
    edited Jan 22, 2014 4:08AM

    If you want for all years just remove input parameter

    CREATE OR REPLACE FUNCTION dyn_row_colum

    RETURN SYS_REFCURSOR

    AS

    v_sqlstr VARCHAR2(32767) := 'SELECT owner,';

    v_rfcur  SYS_REFCURSOR;

    BEGIN

    FOR j_rec IN(SELECT EXTRACT(YEAR FROM created) dt

                  FROM dba_objects

                  GROUP BY EXTRACT(YEAR FROM created))

      LOOP

       v_sqlstr := v_sqlstr||' '||'COUNT(DECODE(EXTRACT(YEAR FROM created),'||''||j_rec.dt||''||',EXTRACT(YEAR FROM created)))'||' '||'"'||j_rec.dt||'"'||',';

      END LOOP;

       v_sqlstr := RTRIM(v_sqlstr,',')||' '||'FROM dba_objects GROUP BY owner ORDER BY 1';

       OPEN v_rfcur FOR v_sqlstr;

       RETURN v_rfcur;

    EXCEPTION

    WHEN OTHERS THEN

      RAISE;

    END dyn_row_colum;


    SELECT dyn_row_colum FROM dual;

    (OR)
    VAR rf_cur REFCURSOR;

    EXEC :rf_cur := dyn_row_colum;

  • KODS
    KODS Member Posts: 145

    I prepared the below query. But it is not working and unable to find where I am doing mistake.

    Please help me.

    select * from (

      select owner,to_char(created,'YYYY') dt from dba_objects)

    pivot(  count(1) for (dt) in (select distinct to_char(created,'YYYY') from dba_objects))

    order by owner;

    ERROR:-

    ------------------------------------------------------------------------------

    SQL> select * from (

      2  select owner,to_char(created,'YYYY') dt from dba_objects)

      3  pivot(  count(*) for (dt) in (select distinct to_char(created,'YYYY') from dba_objects))

      4  order by owner;

    pivot(  count(*) for (dt) in (select distinct to_char(created,'YYYY') from dba_objects))

                                  *

    ERROR at line 3:

    ORA-00936: missing expression

  • AnnEdmund
    AnnEdmund Member Posts: 1,466 Gold Trophy
    edited Jan 23, 2014 5:40AM

    That is what, you cant use subquery in pivot in_clause(You can use it XML pivot only). Please have a look into the below link

    pivot and unpivot queries in 11g

    If you want dynamic pivot you can use above function for dynamic pivot. If you know the values to pivot then you can use sql query to achieve.

This discussion has been closed.