Forum Stats

  • 3,853,207 Users
  • 2,264,192 Discussions
  • 7,905,286 Comments

Discussions

SQL on PIVOT

KODS
KODS Member Posts: 145
edited Jan 22, 2014 2:30AM in SQL & PL/SQL

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;

Tagged:

Best Answer

  • ranit B
    ranit B Member Posts: 3,268 Silver Trophy
    edited Jan 17, 2014 2:48AM Answer ✓

    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;
    
    
    
    
    

Answers

  • ranit B
    ranit B Member Posts: 3,268 Silver Trophy
    edited Jan 17, 2014 2:48AM Answer ✓

    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;
    
    
    
    
    
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited Jan 17, 2014 2:51AM

    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;
    
    
    
    Karthick2003
  • BluShadow
    BluShadow Member, Moderator Posts: 42,326 Red Diamond

    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.

  • ranit B
    ranit B Member Posts: 3,268 Silver Trophy

    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.

  • KODS
    KODS Member Posts: 145

    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

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge

    You are looking for Dynamic pivot.

    From FAQ

    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?
This discussion has been closed.