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;

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;

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;

Karthick2003

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;

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.

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.

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

Karthick2003

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?