Pramod Shivhare

Pramod Shivhare

  • NA
  • 7
  • 8.2k

Ref cursor with dynamic columns

Feb 27 2012 9:26 AM
I am using oracle 11g and have written a sotred procedure which stores values in temporary table as follows:


  id        count        hour        age         range  
-------------------------------------  
  0        5                10         61         10-200
 1         6                20         61         10-200
 2         7                15         61         10-200
 5         9                5          61         201-300
 7         10               25        61         201-300
 0         5                10        62         10-20
 1         6                20        62         10-20
 2         7               15         62         10-20
 5         9                 5         62         21-30
 1         8                 6         62         21-30
 7         10             25         62         21-30
 10       15             30         62         31-40

now using this temp table i want to return two cursors. one for 61 and one for 62(age). and for cursors there distinct range will be columns . for example cursor for age 62 should return following as dataset.



 user         10-20                 21-30                 31-40  
              Count/hour         count/hour         count/hour
   ----------------------------------------------------------
0               5    10                -   -                  -   -
1               6    20                8   6                 -   -
2               7    15                -   -                  -   -
5               -    -                  9   5                  -   -
7               -    -                 10   25                -   -
10             -    -                 -     -                 15   30





this column range in temp table is is not a fixed values these are referenced from other table. can some one plz help.. its urgent. Thnax in advance