Pivot with Dynamic Columns in Oracle

Introduction

Oracle databases have been a stalwart in the world of relational databases for decades, and one of its most powerful features is the ability to dynamically pivot data. In this article, we will explore how to achieve dynamic pivoting in Oracle using stored functions and the SYS_REFCURSOR type.

Background

Dynamic pivoting refers to the process of transforming a set of data from a tabular format to a formatted table where each row represents a unique value for one column. In the context of Oracle, this can be achieved using stored procedures or functions that generate dynamic SQL strings based on the existing data in the database.

One common approach to achieving dynamic pivoting is by using the SYS_REFCURSOR type, which allows us to create stored functions that return a cursor object containing the results of the dynamic query. In this article, we will focus on how to use SYS_REFCURSOR within stored functions to generate dynamic pivot queries.

Creating a Stored Function for Dynamic Pivoting

To achieve dynamic pivoting in Oracle, we can create a stored function that generates a SQL string based on the existing data in the database. This function will take into account the number of unique values in each column and generate a dynamic SQL string accordingly.

Here is an example of how to create such a function:

CREATE OR REPLACE FUNCTION get_passengers_rs RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767);
  v_str       VARCHAR2(32767);
BEGIN
  SELECT LISTAGG('MAX(CASE WHEN rn = '||lvl||' THEN age||''(''||passengers||'')'' END) 
                   AS "Age'||lvl||'"' ,',') WITHIN GROUP (ORDER BY 0)
    INTO v_str
    FROM ( SELECT level AS lvl  
             FROM dual
          CONNECT BY level <= (SELECT MAX(COUNT(*)) FROM t GROUP BY ID ) ) t;  

  v_sql :=
  'SELECT ID, '|| v_str ||'
     FROM 
     (
      SELECT t.*, 
             ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 0) AS rn
        FROM t     
      )
    GROUP BY ID';

  OPEN v_recordset FOR v_sql;
  RETURN v_recordset;
END;

This function uses the LISTAGG function to generate a dynamic SQL string based on the unique values in each column. The CONNECT BY clause is used to get the number of levels (i.e., the number of unique values) for each column, and the resulting value is used to generate the SQL string.

Using the Stored Function

Once we have created the stored function, we can use it to achieve dynamic pivoting in our query:

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

This code creates a cursor object and executes the stored function, printing the result set.

Example Use Case

Let’s consider an example where we have a table t with the following structure:

ID     Passengers Age Eligible
123456 Ben        65  Yes
123456 Mary       58  Yes
123458 Stephanie  37  Yes
123458 Aaron      32  Yes
123458 Caroline   18  No

We want to pivot this data using the Age column, resulting in a table with three columns: Age1, Age2, and Age3. To achieve this using dynamic pivoting, we can use the stored function created earlier:

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

This will generate the following result set:

ID      Age1        Age2            Age3
123456  58(Marie)   65(Ben) 
123458  32(Aaron)   18(Caroline)    37(Stephanie)

As we can see, the stored function has successfully generated a dynamic pivot of the Age column.

Conclusion

In this article, we have explored how to achieve dynamic pivoting in Oracle using stored functions and the SYS_REFCURSOR type. We created a stored function that generates a SQL string based on the existing data in the database and used it to achieve dynamic pivoting in our query. This approach provides flexibility and power when working with large datasets, and can be easily extended to accommodate additional columns or transformations.

Additional Tips and Variations

• To further customize the behavior of the stored function, you can modify the LISTAGG clause to include additional conditions or formatting options.
• You can also use other Oracle functions, such as TO_CHAR or TO_DATE, to perform additional data transformations within the dynamic SQL string.
• To handle cases where there are no unique values in a column, you can modify the stored function to include a default value or NULL handling mechanism.

By following these tips and variations, you can further customize and optimize your stored function for maximum flexibility and power.

Last modified on 2025-03-27