CODEWITHSUNDEEP
oracle-databaseplsqlpivotoracle10gdynamic-pivot
ilhank
ilhank

Reputation: 148

Pivotting on oracle v10.2.0 with two dynamic values

I need to get a query result like this:

        |Person1                  |Person2                  |Person3                  |...
        ------------------------------------------------------------------------------------
Date1   |function(Person1Id,Date1)|function(Person2Id,Date1)|function(Person3Id,Date1)|...         
Date2   |function(Person1Id,Date2)|function(Person2Id,Date2)|function(Person3Id,Date2)|...
Date3   |function(Person1Id,Date3)|function(Person2Id,Date3)|function(Person3Id,Date3)|...
.
.
.

Dates are coming from the user and PersonIds are coming from a table. What I need to is just sending ids and dates o the function and get the result of it. Since I am working on oracle v10.2.0 pivoting does not work and writing case...when statements for each person will not work because there are lots of people in the table I am fetching.

Any help appreciated.

Upvotes: 0

Views: 46

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

You can use Conditional Aggregation within DB version 10g such as

SELECT myDate,
       MAX(CASE WHEN PersonId=1 THEN myfunc(PersonId,myDate) END) AS Person1,
       MAX(CASE WHEN PersonId=2 THEN myfunc(PersonId,myDate) END) AS Person2,
       MAX(CASE WHEN PersonId=3 THEN myfunc(PersonId,myDate) END) AS Person3
  FROM t 
 GROUP BY myDate

Update : Yet, there exists an option, even in DB 10g for dynamic pivoting by using SYS_REFCURSOR, eg. using PL/SQL rather than using SQL only, and show the result set on the command line if you're using SQL Developer. Create a stored function

CREATE OR REPLACE FUNCTION get_person_rs RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767);
  v_str       VARCHAR2(32767);
BEGIN
  WITH tt AS
  ( 
   SELECT PersonId, 
          ROW_NUMBER() OVER (ORDER BY PersonId) AS rn  
     FROM t 
    GROUP BY PersonId 
  )
  SELECT TO_CHAR(RTRIM(XMLAGG(XMLELEMENT(e, 
           'MAX(CASE WHEN PersonId = '||PersonId||
                   ' THEN myfunc(PersonId,myDate) 
                      END) AS Person'||rn         
           , ',')).EXTRACT('//text()').GETCLOBVAL(), ','))
    INTO v_str         
    FROM tt;  

  v_sql :=
  'SELECT myDate, '|| v_str ||'
     FROM t
    GROUP BY myDate';

  OPEN v_recordset FOR v_sql;
  RETURN v_recordset;
END;

in which, ROW_NUMBER() Analytic function, which is available in 10g, is used, but LISTAGG() string aggregation function is not yet in 10g. So XMLAGG is used instead. This generated SQL string within the function is also exactly same as the above one, eg. in Conditionally Aggregated Logic.

Then run the below code :

VAR rc REFCURSOR
EXEC :rc := get_person_rs;
PRINT rc

from SQL Developer's Command Line in order to see the expected result set dynamically generated by currently existing data.

Upvotes: 1

Related Questions