Generic Pivot Function

Many folks ask how to do a “dynamic pivot”. It’s often best to use a preliminary query to generate the PIVOT clause. Here’s a generic function to do just that.

[UPDATE 2018-07-31: for advanced use cases, I wrote a more powerful function called ADVANCED_PIVOT, which I explain here: Improved PIVOT Function]

Anatomy of PIVOT

PIVOT clause from Oracle documentation

Let’s take a simple example, getting the average salary by department and by job:

select dname, job, avg(sal) avg_sal
from DEPT join EMP using(DEPTNO)
group by dname, job
order by 1,2;
DNAME JOB AVG_SAL
ACCOUNTING CLERK 1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
RESEARCH ANALYST 3000
RESEARCH CLERK 950
RESEARCH MANAGER 2975
SALES CLERK 950
SALES MANAGER 2850
SALES SALESMAN 1400

 

We want three rows, one per department, and five “pivot columns” for each of the five distinct jobs. The JOB column data become pivot column names and the SAL column data become pivot column data. The desired result is:

DNAME ANALYST CLERK MANAGER PRESIDENT SALESMAN
ACCOUNTING 1300 2450 5000
RESEARCH 3000 950 2975
SALES 950 2850 1400

 

A PIVOT clause needs:

  1. One row source for input: a table, view or query in parentheses. Each column in the input must have a unique name. The row source does not need to do a GROUP BY: the PIVOT clause will do the aggregation.
  2. One or more aggregate functions: they produce the data for the pivot columns.
  3. a PIVOT_FOR clause with one or more columns whose values will correspond to new column names.
  4. a PIVOT_IN clause that lists the pivot columns with their source values.
select * from (
  select dname, job, sal from DEPT join EMP using(DEPTNO)
)
pivot(
  AVG(SAL)
  for JOB
  in (
    'ANALYST' as "ANALYST",
    'CLERK' as "CLERK",
    'MANAGER' as "MANAGER",
    'PRESIDENT' as "PRESIDENT",
    'SALESMAN' as "SALESMAN"
  )
) order by 1
  • line 2 is the input.
  • line 5 is the aggregate function.
    There will be an implicit GROUP BY of DNAME (because not mentioned in the PIVOT) and JOB (because mentioned in the PIVOT_FOR clause).
  • line 6 is the PIVOT_FOR clause: JOB
  • lines 7 through 13 are the PIVOT_IN clause.
    In line 8, a pivot column called ANALYST is defined for when JOB = ‘ANALYST’. The result of the AVG(SAL) function for DNAME = ‘RESEARCH’ and JOB = ‘ANALYST’ will go into the ‘RESEARCH’ row and the ANALYST column.

A Generic PIVOT Template

I like to use what I call “SQL templates“. They are a way of describing a pattern: you distinguish what varies each time from what stays the same, and you put placeholders where the variable stuff should go. For PIVOT, the template has four placeholders.

select * from #SOURCE#
pivot(#AGGFUNCS# for #PIVOTFOR# in (
#PIVOTINLIST#
)) order by 1,2
  1. #SOURCE# is the row source: table, view, query in parentheses.
  2. #AGGFUNCS# is one or more aggregate functions separated by commas, with or without aliases.
  3. #PIVOTFOR# must contain exactly one column name from the source. This is a limitation of my function.
  4. #PIVOTINLIST# lists the pivot columns.

My function gets the first three placeholders from the input and generates the fourth one.

Generating the value for #PIVOTINLIST#

The problem with PIVOT is that the list of pivot columns depends on the data, so we need to know the data before querying it! In our example, we list five column names for the five JOBs in the EMP table, but how do we know what those jobs are?

My function uses a preliminary query to get that list. It has its own template using two of the same placeholders:

q'£select listagg('''' || #PIVOTFOR# || ''' as "' || #PIVOTFOR# || '"', ',')
within group(order by #PIVOTFOR#)
from (select distinct #PIVOTFOR# from #SOURCE#)£'

The Code

create or replace function generic_pivot(
  p_source in varchar2,   -- table, view or query in parentheses
  p_pivotfor in varchar2, -- one column from the input
  p_aggfuncs in varchar2  -- one or more aggregation functions
) return sys_refcursor is

-- Calculates pivot_in_list using SQL 1, updates SQL 2 text and opens ref cursor.
-- Pivot_for_clause can contain one column

  l_sql sys.odcivarchar2list := sys.odcivarchar2list(

q'£select listagg('''' || #PIVOTFOR# || ''' as "' || #PIVOTFOR# || '"', ',')
within group(order by #PIVOTFOR#)
from (select distinct #PIVOTFOR# from #SOURCE#)£',

'select * from #SOURCE#
pivot(#AGGFUNCS# for #PIVOTFOR# in (
#PIVOTINLIST#
)) order by 1,2'
  );
  l_refcur sys_refcursor;
  l_pivotinlist varchar2(32767);
begin
  for i in 1..l_sql.count loop
    l_sql(i) := replace(l_sql(i), '#SOURCE#', p_source);
    l_sql(i) := replace(l_sql(i), '#PIVOTFOR#', p_pivotfor);
  end loop;
  dbms_output.put_line(l_sql(1));
  dbms_output.put_line('/');
  open l_refcur for l_sql(1);
  fetch l_refcur into l_pivotinlist;
  close l_refcur;
  l_sql(2) := replace(l_sql(2), '#AGGFUNCS#', p_aggfuncs);
  l_sql(2) := replace(l_sql(2), '#PIVOTINLIST#', l_pivotinlist);
  dbms_output.put_line(l_sql(2));
  dbms_output.put_line('/');
  open l_refcur for l_sql(2);
  return l_refcur;
end generic_pivot;
/

Test results

SQL> set serveroutput off
SQL> var rc refcursor
SQL> begin
  2    :rc := generic_pivot(
  3      '(select dname, job, sal from DEPT join EMP using(DEPTNO))', 
  4      'JOB', 
  5      'AVG(SAL)'
  6    );
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> print :rc

DNAME             ANALYST      CLERK    MANAGER  PRESIDENT   SALESMAN
-------------- ---------- ---------- ---------- ---------- ----------
ACCOUNTING                      1300       2450       5000           
RESEARCH             3000        950       2975                      
SALES                            950       2850                  1400


SQL> begin
  2    :rc := generic_pivot(
  3      '(select dname, job, sal from DEPT join EMP using(DEPTNO))', 
  4      'DNAME', 
  5      'AVG(SAL)'
  6    );
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> print :rc

JOB       ACCOUNTING   RESEARCH      SALES
--------- ---------- ---------- ----------
ANALYST                    3000           
CLERK           1300        950        950
MANAGER         2450       2975       2850
PRESIDENT       5000                      
SALESMAN                              1400


SQL> begin
  2    :rc := generic_pivot(
  3      '(select dname, job, sal from DEPT join EMP using(DEPTNO))', 
  4      'JOB', 
  5      'MIN(SAL) min, MAX(SAL) max'
  6    );
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> print :rc

DNAME          ANALYST_MIN ANALYST_MAX  CLERK_MIN  CLERK_MAX MANAGER_MIN MANAGER_MAX PRESIDENT_MIN PRESIDENT_MAX SALESMAN_MIN SALESMAN_MAX
-------------- ----------- ----------- ---------- ---------- ----------- ----------- ------------- ------------- ------------ ------------
ACCOUNTING                                   1300       1300        2450        2450          5000          5000                          
RESEARCH              3000        3000        800       1100        2975        2975                                                      
SALES                                         950        950        2850        2850                                     1250         1600

Hope this helps!

Advertisements

3 thoughts on “Generic Pivot Function

  1. It is worth mentioning the usability of a PL/SQL function that returns a cursor with a variable number of columns. You cannot easily fetch such cursor in PL/SQL code (only in Dynamic PL/SQL) as the structure of the cursor is not know at compilation time. At the same time, you can use this approach in reporting applications. For that matter you will need to turn your function returning a cursor into a table function. This was done by Anton Sheffer in a famous blog post back to 2006: https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

    The challenge with your function, Stew, is that outside of SQL*Plus where you can conveniently print the cursor variable, it has very little usability. I have faced the same issue with a similar table function (that returned variable number of columns) – https://asktom.oracle.com/pls/apex/asktom.search?tag=how-to-make-a-use-of-pipelined-table-functions-that-implement-odci-table-interface-in-plsql

    • Zahar,

      You are exaggerating quite a bit. A function that returns a REF CURSOR is the classic way of returning data to applications, recommended by Tom Kyte for years and now Bryn Llewellyn. For many years, client programs written in Java or many other languages have been able to consume REF CURSORs from PL/SQL functions. There is no need to turn the cursor into a table function.

      You are right that PL/SQL, which is oriented toward static SQL, has problems consuming data from REF CURSORs. Even Anton’s solution is not completely dynamic: if the data changes the number of columns, the SELECT must be reparsed for the new columns to be picked up.

      The “challenge” with the PIVOT clause is that the pivot columns are inherently dynamic because they depend entirely on the data. That “challenge” is not specific to my function, it’s the nature of PIVOT.

      We’ll see if polymorphic table functions do a better job of this.

      Best regards,
      Stew

  2. Stew, I see your point. Yes, you can and should use REF CURSOR for returning the result sets to a client. It will just require additional coding in a procedural language to fetch that cursor.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s