Improved PIVOT Function

My generic PIVOT function only allowed one column in the FOR clause. The modestly named “Advanced” function has no such limit.

Here are some test runs to put it through its paces. I’ll save the code for the end.

UPDATE 2018-05-31: I added support for horizontal and vertical totals. The most recent code is still at the bottom of this post, but the additional explanations are here: PIVOT Function with Totals

NOTE: I changed the order of the input parameters to match the order of the PIVOT statement itself: aggregation functions before the pivot_for_clause.

One aggregation function, one FOR column

SQL> set serveroutput off
SQL> var rc refcursor
SQL> begin
  2    :rc := advanced_pivot(
  3      '(select dname, job, sal from DEPT join EMP using(DEPTNO))', 
  4      'AVG(SAL)', 
  5      'JOB'
  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

Transposing the data and aliasing the aggregate

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

PL/SQL procedure successfully completed.

SQL> print :rc


JOB       ACCOUNTING_AVG_SAL RESEARCH_AVG_SAL SALES_AVG_SAL
--------- ------------------ ---------------- -------------
ANALYST                                  3000              
CLERK                   1300              950           950
MANAGER                 2450             2975          2850
PRESIDENT               5000                               
SALESMAN                                               1400

One aggregate, two FOR columns

The pivot columns are ordered by DNAME, JOB from left to right.

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

PL/SQL procedure successfully completed.

SQL> print :rc


ACCOUNTING_CLERK_MAX_SAL ACCOUNTING_MANAGER_MAX_SAL ACCOUNTING_PRESIDENT_MAX_SAL RESEARCH_ANALYST_MAX_SAL RESEARCH_CLERK_MAX_SAL RESEARCH_MANAGER_MAX_SAL SALES_CLERK_MAX_SAL SALES_MANAGER_MAX_SAL SALES_SALESMAN_MAX_SAL
------------------------ -------------------------- ---------------------------- ------------------------ ---------------------- ------------------------ ------------------- --------------------- ----------------------
                    1300                       2450                         5000                     3000                   1100                     2975                 950                  2850                   1600

Shorter pivot column names

The pivot column names can get long real fast. With this generic function, the way to shorten the names is to shorten the input data.

SQL> begin
  2    :rc := advanced_pivot(
  3      '(select initcap(substr(dname,1,4)) dname, initcap(substr(job,1,5)) job, sal from DEPT join EMP using(DEPTNO))', 
  4      'MIN(SAL) "Min_Sal"', 
  5      'JOB,DNAME'
  6    );
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> print :rc


Analy_Rese_Min_Sal Clerk_Acco_Min_Sal Clerk_Rese_Min_Sal Clerk_Sale_Min_Sal Manag_Acco_Min_Sal Manag_Rese_Min_Sal Manag_Sale_Min_Sal Presi_Acco_Min_Sal Sales_Sale_Min_Sal
------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------ ------------------
              3000               1300                800                950               2450               2975               2850               5000               1250

Two aggregates, two FOR columns

I would rather not do this by hand…

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

PL/SQL procedure successfully completed.

SQL> print :rc


     AC_CL  AC_CL_AVG      AC_MA  AC_MA_AVG      AC_PR  AC_PR_AVG      RE_AN  RE_AN_AVG      RE_CL  RE_CL_AVG      RE_MA  RE_MA_AVG      SA_CL  SA_CL_AVG      SA_MA  SA_MA_AVG      SA_SA  SA_SA_AVG
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1       1300          1       2450          1       5000          1       3000          2        950          1       2975          1        950          1       2850          4       1400

What if the FOR column is a date?

I want to break down the number of hires in each department by month, so why not truncate HIREDATE by month, then just display the month and the year?

SQL> begin
  2    :rc := advanced_pivot(
  3      '(select dname, to_char(trunc(hiredate,''MM''),''MON-yyyy'') hiredate from DEPT join EMP using(DEPTNO))', 
  4      'count(*)', 
  5      'hiredate'
  6    );
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> print :rc


DNAME            APR-1981   DEC-1980   DEC-1981   FEB-1981   JAN-1982   JUN-1981   MAY-1981   MAY-1987   NOV-1981   SEP-1981
-------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
ACCOUNTING              0          0          0          0          1          1          0          0          1          0
RESEARCH                1          1          1          0          0          0          0          1          0          0
SALES                   0          0          1          2          0          0          1          0          0          2

Oops! The input HIREDATE is now a string, so the pivot column names are ordered by the string values.

Ordering dates in date order

Here is a situation where implicit conversion is your friend. Just keep the truncated date in your input query and set NLS_DATE_FORMAT as you wish. I’ll order the columns by date and Oracle will convert the dates to strings in the format you specified.

SQL> alter session set nls_date_format='mon-yyyy';

Session altered.

SQL> begin
  2    :rc := advanced_pivot(
  3      '(select dname, trunc(hiredate,''MM'') hiredate from DEPT join EMP using(DEPTNO))', 
  4      'count(*)', 
  5      'hiredate'
  6    );
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> print :rc


DNAME            dec-1980   feb-1981   apr-1981   may-1981   jun-1981   sep-1981   nov-1981   dec-1981   jan-1982   may-1987
-------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
ACCOUNTING              0          0          0          0          1          0          1          0          1          0
RESEARCH                1          0          1          0          0          0          0          1          0          1
SALES                   0          2          0          1          0          2          0          1          0          0

The Code

create or replace function advanced_pivot(
  p_source in varchar2,       -- table, view or query in parentheses
  p_aggfuncs in varchar2,     -- one or more aggregation functions with or without aliases
  p_pivotfor in varchar2,     -- one or more columns from the input separated by commas
  p_total_label in varchar2 default null, -- label to use for pivot totals (if CUBE, ROLLUP, etc.)
  p_ordercol in varchar2 default null     -- column(s) to order by with p_total_label last
) return sys_refcursor authid current_user is
/*
Calculates pivot_in_list using SQL 1, updates SQL 2 text and opens ref cursor.
- Pivot_in_list concatenates values from all PIVOT_FOR columns
  for example, if (DNAME,JOB) then
    ('ACCOUNTING','CLERK') as "ACCOUNTING_CLERK",
    ('ACCOUNTING','MANAGER') as "ACCOUNTING_MANAGER",
  and so on
*/
  l_sql sys.odcivarchar2list := sys.odcivarchar2list(
  
q'@select listagg('(''' || expr || ''') as "' || al || '"', ',
')
within group(order by #ORDERFOR#)
from (select distinct
#EXPR# expr,
#ALIAS# al,
#PIVOTFOR#
from #SOURCE#)@',

'select * from #SOURCE#
pivot(#AGGFUNCS# for (#PIVOTFOR#) in (
#PIVOTINLIST#
)) order by #ORDERCOL#1,2,3'
  );
  l_pivotfor varchar2(255);
  l_orderfor varchar2(255);
  l_refcur sys_refcursor;
  l_pivotinlist varchar2(32767);
  l_expr varchar2(4000);
  l_alias varchar2(4000);
  l_ordercol varchar2(255);
begin
  l_pivotfor := trim( ',' from replace(p_pivotfor,' ') );
  l_orderfor := 
    case when p_total_label is null 
      then l_pivotfor
      else 'nullif(' 
        || replace(l_pivotfor, ',', ','''||p_total_label||'''),nullif(') 
        || ','''||p_total_label||''')'
    end;
  l_sql(1) := replace(l_sql(1), '#ORDERFOR#', l_orderfor);
  
  l_expr := replace(l_pivotfor,',',q'%||''','''||%');
  l_sql(1) := replace(l_sql(1), '#EXPR#', l_expr);
  
  l_alias := replace(l_pivotfor,',',q'%||'_'||%');
  l_sql(1) := replace(l_sql(1), '#ALIAS#', l_alias);
  
  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#', l_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);
  
  
  l_ordercol := trim( ',' from replace(p_ordercol,' ') );
  l_ordercol :=
    case when p_total_label is null or l_ordercol is null
      then l_ordercol
      else 'nullif(' 
        || replace(l_ordercol, ',', ','''||p_total_label||'''),nullif(') 
        || ','''||p_total_label||'''),'
    end;
  l_sql(2) := replace(l_sql(2), '#ORDERCOL#', l_ordercol);
  
  dbms_output.put_line(l_sql(2));
  dbms_output.put_line('/');
  open l_refcur for l_sql(2);
  return l_refcur;
  
end advanced_pivot;
/

I really enjoyed the challenge of writing a truly generic function. I hope you enjoy using it!

Advertisements

One thought on “Improved PIVOT Function

  1. Hello Stew,

    Each of your posts is a piece of art, I can only keep wondering and wishing to myself and all others to read and enjoy
    as many as possible like this one :) :)

    Cheers & Best Regards,
    Iudith Mentzel

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 )

w

Connecting to %s