6 Replies Latest reply: Jan 22, 2014 1:30 AM by Karthick_Arp RSS

    SQL on PIVOT

    KODS

      Hi All,

       

      I am trying to write a SQL which will generate report which shows by user on each month how many objects created.

       

      select owner, to_char(created,'MON'), count(1) cnt

      from dba_objects

      group by owner, to_char(created,'MON') order by 1,2;

       

      The above query is giving report. Now I like to see the report in more readable format using PIVOT. I prepared a query but it is throwing error. Please help me in preparing correctly.

       

      select * from (

        select owner,to_char(created,'MON') from dba_object)

      pivot(

        count(to_char(created,'MON')) for to_char(created,'MON') in ('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'))

      order by owner;

        • 1. Re: SQL on PIVOT
          ranit B

          Hi,

           

          Please mention what is the error you are getting.

           

          And, the database version? Note - PIVOT is not supported on databases < 11g

           

          Please check if this help -

          select * from (

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

          pivot(

            count(1) for (dt) in ('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')

          )

          order by owner;

          • 2. Re: SQL on PIVOT
            Karthick_Arp

            Try this.

             

            select *

              from (

                     select owner

                          , to_number(to_char(created,'YYYY')) created_year

                          , to_char(created,'MON') created_month

                       from dba_objects

                   )

            pivot (

                     count(*) for created_month in ('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')

                   )

            order

                by owner

                 , created_year;

             

            • 3. Re: SQL on PIVOT
              BluShadow

              Ranit,

               

              Just for sanity's sake... you're better to use count(*) than count(1).

               

              a) For readability... it makes more sense to say "Count everything" (* = everything) than to say "Count one".  If you had a bag of balls and asked a child to count 1 from the bag, they would pull out 1 ball, if you did the same but asked them to count everything, they would count all the balls.

              b) Oracle understands what you want with count(1), but internally it re-writes it as count(*) before the query is executed.  Save oracle the bother of doing that.

               

              Re: 6. What is the difference between count(*) and count(1)?

              • 4. Re: SQL on PIVOT
                ranit B

                Thanks a lot, Blu.

                 

                It feels always great when I get rectified and learn new stuff.

                If you had a bag of balls and asked a child to count 1 from the bag, they would pull out 1 ball, if you did the same but asked them to count everything, they would count all the balls

                This was too cool. Nice example.

                 

                -- Ranit

                an Oracle enthusiast.

                • 5. Re: SQL on PIVOT
                  KODS

                  Hi Ranit,

                   

                  can you help me in generating for years? I mean SQL to find the objects year wise instead of month wise. But I am not interested to put specific years. Generic SQL query which generaes for all the years in the database where the objects created.

                   

                  I prepared it for specific years by changing MON to YYYY and passing specific years.

                   

                  Thanks

                  Kods

                  • 6. Re: SQL on PIVOT
                    Karthick_Arp

                    You are looking for Dynamic pivot.

                     

                    From FAQ Re: 4. How do I convert rows to columns?

                     

                    Dynamic Pivoting

                    "But I want to pivot an unknown number of rows to columns?"
                    This is not easily possible as the number of columns returned by an SQL must be known before any data is fetched, it would have to be done dynamically.

                    See these threads:


                    PL/SQL 101 : Cursors and SQL Projection

                    Help for a query to add columns

                    How to pipeline a function with a dynamic number of columns?