13 Replies Latest reply: Jan 23, 2014 11:40 AM by Sven W. RSS

    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

        • 1. Re: sql to generate yearly report
          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.

          • 2. Re: sql to generate yearly report
            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

            • 3. Re: sql to generate yearly report
              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));

              • 4. Re: sql to generate yearly report
                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()]

                        );

                • 5. Re: sql to generate yearly report
                  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

                  • 6. Re: sql to generate yearly report
                    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.

                    • 7. Re: sql to generate yearly report
                      AnnPricks E

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

                      • 8. Re: sql to generate yearly report
                        AnnPricks E

                        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;

                        • 9. Re: sql to generate yearly report
                          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

                          • 10. Re: sql to generate yearly report
                            AnnPricks E

                            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.

                            • 11. Re: sql to generate yearly report
                              Partha Sarathy S

                              This is the reason why I gave PIVOT XML because you won't be able to use queries inside PIVOT. For PIVOT you need to explicitly mention the values.

                              • 12. Re: sql to generate yearly report
                                BluShadow

                                You obviously don't undersand what SQL Projection is...

                                I suggest you read the follow:

                                 

                                PL/SQL 101 : Cursors and SQL Projection

                                 

                                In short, you cannot have a dynamic number of columns in a query that are based on the data, because the number of columns in the output must be known by the query before it queries any data.

                                 

                                The only way to have a dynamic number of columns is to write dynamic queries (or as shown generate the data into a structured datatype like XML), and then you'll have to have code that can dynamically process that data.... and all of a sudden your whole process has to become dynamic... making it harder to debug, more prone to errors, and less secure and scalable.

                                 

                                What you are wanting to achieve it typically done when you are generating reports, and for that, Report writing tools are best suited for that task (rather than SQL).  They will query the data first and then process the data to determine how to lay it out on a report with a dynamic number of columns and rows as required.  SQL cannot natively do dynamic numbers of columns (there are ways, but it's very complex)

                                • 13. Re: sql to generate yearly report
                                  Sven W.

                                  Why not simply add the year to the query?

                                   

                                  select * from (
                                      select owner, to_char(created,'YYYY') yr, 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 yr, owner
                                  

                                  The year is just another column. The years are ordered one below the other.

                                  This makes the data much better to read then when printing one year right or left to the next one.