sql to generate yearly report

KODS

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

Manik

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

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

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

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

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

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

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

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

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

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.