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)
count(1) for (dt) in ('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC')
order by owner;
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.
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.
an Oracle enthusiast.
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.
You are looking for Dynamic pivot.
"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: