vsbabu.org

Pivoting on ALL_OBJECTS

Some times, in the RDBMS world, you need to generate rows from a SQL where not enough rows exist in relevant tables. For examples, you might want to print a range of numbers like 1-10, 11-20, 21-30 and so on, and show the records from another table that falls in these ranges (eg: number of students in a class).

A classic solution is using a dummy table that has one column with many records with values say 1 to 1000. This is often called a pivot table.

With Oracle 8i and above, you can use sub-queries in FROM clauses and even in SELECT lists. This way, you don’t really need to maintain a pivot table, as Oracle Guru Tom Kyte illustrates in two solutions this week.

Oracle has a view named ALL_OBJECTS that gives you a list of every object in the instance. Here’s a session from a small instance.

SQL> select count(*) from all_objects;

  COUNT(*)
----------
     22761

SQL> select r from (select rownum r from all_objects) where r < 10;

         R
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

Using ROWNUM, a pseudo-column in Oracle, I can easily generate upto 22761 records from ALL_OBJECTS. For higher numbers, I can extend this by joining ALL_OBJECTS with other tables or views. By the way, if you are too adventurous and decide to join this view with itself to generate a huge number of rows, please don’t tell your boss it was my idea, if the performance takes a nose dive ;-).

Tom has two very interesting examples involving this scheme. First one is a simple one, where he explains how to print all dates between two dates.

The second one is very slick. He explains how to calculate hours worked between two dates. In my opinion, the SQL solution is a gem.

Similarly, Tom explains how to use ROWNUM to paginate a resultset. Andrej Koelewijn analyzes this with respect to Java’s scrollable resultsets.

Tom Kyte has some more mind-boggling examples of pivot queries - warning: if you are not in the best of the moods, this can produce serious head ache

1. This is another Pivot table question. I have this table in an Oracle database that I want to pivot in MS Access using an SQL pass through query (I think the Oracle SQL applies in Access) because I need to use this data in crystal reports

   This is what I have so far:

   SELECT AAC_EVENT.RES_ID, Sum(AAC_EVENT.DURATION) "Duration", AAC_EVENT.DATE_STAMP, AAC_EVENT.SUB_EVENT_TYPE
   FROM AAC_EVENT
   GROUP BY AAC_EVENT.RES_ID, AAC_EVENT.SUB_EVENT_TYPE, AAC_EVENT.DATE_STAMP

   I need SUB_EVENT_TYPE as the column heading, these numbers are limited and are set in stone (0-11).

   I need RES_ID and DATE_STAMP as the rows and Duration is the value.

   Do you know of a way to do this.

   Many thanks for you help.
   Giles Cardew

   Posted by: Giles Cardew on January 15, 2004 09:16 AM

2. In Oracle, it can be easily done using DECODE. Access doesn't have DECODE, I don't know if it has something similar.

   SELECT AAC_EVENT.RES_ID,
   AAC_EVENT.DATE_STAMP,
   SUM(DECODE(AAC_EVENT.SUB_EVENT_TYPE, 0, AAC_EVENT.DURATION, 0)) EVENT_TYPE_0,
   SUM(DECODE(AAC_EVENT.SUB_EVENT_TYPE, 1, AAC_EVENT.DURATION, 0)) EVENT_TYPE_1,
   SUM(DECODE(AAC_EVENT.SUB_EVENT_TYPE, 2, AAC_EVENT.DURATION, 0)) EVENT_TYPE_2,
   /* and so on till 11 */
   SUM(DECODE(AAC_EVENT.SUB_EVENT_TYPE, 11, AAC_EVENT.DURATION, 0)) EVENT_TYPE_11
   FROM AAC_EVENT
   GROUP BY AAC_EVENT.RES_ID, AAC_EVENT.DATE_STAMP
   

   Posted by: Babu on January 15, 2004 12:34 PM