CODEWITHSUNDEEP
oracle11g
Pramod
Pramod

Reputation: 667

Ref cursor with dynamic columns

I am using oracle 11g and have written a stored 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.

edited: i am using PIVOT for above problem, all examples i saw in internet are there for fixed values of column values (range in my case). how can i get dynamic values. following is the ex query:

SELECT *
FROM   (SELECT column_2, column_1
    FROM   test_table)
PIVOT  (SUM(column1) AS sum_values FOR (column_2) IN ('value1' AS a, 'value2' AS b, 'value3' AS c));

Instead of using handwritten value i am using following query inside 'IN'

SELECT * from(
with x as (
  SELECT DISTINCT range 
          FROM test_table
         WHERE age = 62 )
select ltrim( max( sys_connect_by_path(range, ','))
                keep (dense_rank last order by curr),
              ',') range
  from (select range,
               row_number() over (order by range) as curr,
               row_number() over (order by range) -1 as prev
          from x)
connect by prev = PRIOR curr
  start with curr = 1 )

it is giving error in this case. But when i using handwritten values its giving right output.

select * from (select user_id, nvl(count,0) count, nvl(hour,0) hour,nvl(range,0) range,nvl(age,0) 
age from test_table)
  PIVOT (SUM(count) as sum_count, sum(hour) as sum_hour for (range) IN 
(

'10-20','21-30','31-40' 
 )
) where age = 62 order by userid

how can i give values dynamically there? how can i do it.

Upvotes: 0

Views: 1765

Answers (1)

Timeout
Timeout

Reputation: 7909

Cursors are slow, I would recommend trying to do this in a query unless there's no alternative (or speed doesn't matter). You may want to look into: PIVOT / UNPIVOT which can rotate columns (in this case "range").

Here's some PIVOT / UNPIVOT documentation and examples:
http://www.oracle-developer.net/display.php?id=506

Based on your last edit:

Pretty sure you have two options:

  1. Build dynamic sql based on the distinct values found in the "range" column.

    You'll probably be stuck using a cursor again to build the column names but at least it will be limited to just the distinct ranges.

  2. Oracle has a PIVOT XML command that you can use for this.

    See: http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html

    And scroll down to the section: "XML Type"

Upvotes: 2

Related Questions