SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL on PIVOT

KODSJan 17 2014 — edited Jan 22 2014

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;

This post has been answered by 915396 on Jan 17 2014

Jump to Answer

915396

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;

Marked as Answer by KODS · Sep 27 2020

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;

1 person found this helpful

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.

915396

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?

1 - 6

Locked Post

New comments cannot be posted to this locked post.

Locked on Feb 19 2014

Added on Jan 17 2014

#pivot, #query, #sql

6 comments

265 views

SQL & PL/SQL

SQL on PIVOT

Comments

Post Details