My Presentations at #UKOUG #techfest19

I just finished my second presentation at TechFest19 in sunny Brighton, England. The conference this year is great from every point of view: venue, people, content, conversations… Thanks to those who came to my talks for your benevolent attention and excellent questions/remarks!

Both my presentations are now on SlideShare. I beg you to download them so the animations work; if you don’t, you will see a jumble.

Best wishes to all for the end of the conference and the trip home.

Regards, Stew

Make your Data Dance: UNPIVOT, PIVOT and GROUP BY extensions

JSON in Oracle database 18c and 19c

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!