Oracle 11g Pivot, Unpivot Features

In version 11g, the Pivot / Unpivot functions appeared (which first appeared in MS SQL 2005), which allow us to dynamically distribute the vertical data across the columns as we prefer.



Suppose you have a customers table:

  SQL> desc customers
 Name Null?  Type
 ----------------------------------------- -------- - --------------------------
 CUST_ID NUMBER (10)
 CUST_NAME VARCHAR2 (20)
 STATE_CODE VARCHAR2 (2)
 TIMES_PURCHASED NUMBER (3) 

Where is the sample

select cust_id, state_code, times_purchased
from customers
order by cust_id;

shows the customer ID, state code, and how many times he bought something:

  CUST_ID STATE_CODE TIMES_PURCHASED
 ------- ---------- ---------------
 1 CT 1
 2 NY 10
 3 NJ 2
 4 ny 4 

We need to know the number of customers grouped by state and by the number of their orders:

select state_code, times_purchased, count (1) cnt
from customers
group by state_code, times_purchased;

  ST TIMES_PURCHASED CNT
 - --------------- ----------
 CT 0 90
 CT 1 165
 CT 2 179
 CT 3 173
 CT 4 173
 CT 5 152
 ... 

This request returns what we need, but it would be much more convenient in this form:

  Times_purch CT NY NJ ...
 1 0 1 0 ...
 2 23 119 37 ...
 3 17 45 1 ...
 ... 

Before version 11g, this would have to be repeated many times sum (decode (state_code, 'CT', 1.0) “CT”, sum (decode (state_code, 'NY', 1.0) “NY”, ... But thanks to the pivot function we can do it simply:

select * from (
select times_purchased as "Puchase Frequency", state_code
from customers t
) pivot (
count (state_code)
for state_code in ('NY' as "New York", 'CT' "Connecticut", 'NJ' "New Jersey", 'FL' "Florida", 'MO' as "Missouri")
)
order by 1
/

  Puchase Frequency New York Connecticut New Jersey Florida Missouri
 ----------------- ---------- ----------- ---------- - -------- ----------
 0 16601 90 0 0 0
 1 33048 165 0 0 0
 2 33151 179 0 0 0
 3 32978 173 0 0 0
 4 33109 173 0 1 0
 ... 

The Unpivot function performs the opposite transformation.

For those who have not yet migrated to 11g, I can offer my modified Tom Kite code :
')

create or replace type varchar2_table as table of varchar2(4000);
/
create or replace package PKG_PIVOT is

function pivot_sql (
p_max_cols_query in varchar2 default null
, p_query in varchar2
, p_anchor in varchar2_table
, p_pivot in varchar2_table
, p_pivot_head_sql in varchar2_table default varchar2_table()
)
return varchar2;

function pivot_ref (
p_max_cols_query in varchar2 default null
, p_query in varchar2
, p_anchor in varchar2_table
, p_pivot in varchar2_table
, p_pivot_name in varchar2_table default varchar2_table()
)
return sys_refcursor;

end PKG_PIVOT;
/
create or replace package body PKG_PIVOT is
/**
* Function returning query
*/
function pivot_sql (
p_max_cols_query in varchar2 default null
, p_query in varchar2
, p_anchor in varchar2_table
, p_pivot in varchar2_table
, p_pivot_head_sql in varchar2_table
) return varchar2
is
l_max_cols number;
l_query varchar2(4000);
l_pivot_name varchar2_table:=varchar2_table();
k integer ;
c1 sys_refcursor;
v varchar2(30);
begin
-- -
if (p_max_cols_query is not null ) then
execute immediate p_max_cols_query
into l_max_cols;
else
raise_application_error (-20001, 'Cannot figure out max cols' );
end if ;

--
l_query := 'select ' ;

for i in 1 .. p_anchor. count loop
l_query := l_query || p_anchor (i) || ',' ;
end loop;
--
k:=1;
if p_pivot_head_sql. count =p_pivot. count
then
for j in 1 .. p_pivot. count loop
open c1 for p_pivot_head_sql(j);
loop
fetch c1 into v;
l_pivot_name.extend(1);
l_pivot_name(k):=v;
EXIT WHEN c1%NOTFOUND;
k:=k+1;
end loop;
end loop;
end if ;

--
-- "max(decode(rn,1,C{X+1},null)) c_name+1_1"
for i in 1 .. l_max_cols loop
for j in 1 .. p_pivot. count loop
l_query := l_query || 'max(decode(rn,' || i || ',' || p_pivot (j) || ',null)) '
|| '"' ||l_pivot_name ((j-1)*l_max_cols+i) || '"' || ',' ;
end loop;
end loop;

--
l_query := rtrim (l_query, ',' ) || ' from ( ' || p_query || ') group by ' ;

--
for i in 1 .. p_anchor. count loop
l_query := l_query || p_anchor (i) || ',' ;
end loop;

l_query := rtrim (l_query, ',' );

-- SQL
return l_query;
end ;

/**
*
*/
function pivot_ref (
p_max_cols_query in varchar2 default null
, p_query in varchar2
, p_anchor in varchar2_table
, p_pivot in varchar2_table
, p_pivot_name in varchar2_table
) return sys_refcursor
is
p_cursor sys_refcursor;
begin
execute immediate 'alter session set cursor_sharing=force' ;
open p_cursor for pkg_pivot.pivot_sql (
p_max_cols_query
, p_query
, p_anchor
, p_pivot
, p_pivot_name
);
execute immediate 'alter session set cursor_sharing=exact' ;
return p_cursor;
end ;
end PKG_PIVOT;
/



Example of use:

begin
:qq:=pkg_pivot.pivot_sql(
'select count(distinct trunc(dt)) from actions'
, 'select e.name name,sum(a.cnt) sum_cnt,a.dt,dense_rank() over(order by dt) rn from actions a left join emp e on e.id=a.emp group by e.name,a.dt'
, varchar2_table( 'NAME' )
, varchar2_table( 'SUM_CNT' )
, varchar2_table( 'select distinct ' 'Date ' '||trunc(dt) from actions' )
);
:qc :=pkg_pivot.pivot_ref(
'select count(distinct trunc(dt)) from actions'
, 'select e.name,sum(a.cnt) sum_cnt,a.dt,dense_rank() over(order by dt) rn from actions a left join emp e on e.id=a.emp group by e.name,a.dt'
, varchar2_table( 'NAME' )
, varchar2_table( 'SUM_CNT' )
, varchar2_table( 'select distinct ' 'Date ' '||trunc(dt) from actions' )
);
end ;

* This source code was highlighted with Source Code Highlighter .

Result: