機能Oracle 11g Pivot、Unpivot

バージョン11gでは、ピボット/アンピボット関数が登場しました（MS SQL 2005で最初に登場しました）。これにより、好きなように列の垂直データを動的に並べ替えることができます。



顧客テーブルがあるとします：

  SQL> descのお客様
名前ヌル？ 種類
 ----------------------------------------- --------- --------------------------
 CUST_ID NUMBER（10）
 CUST_NAME VARCHAR2（20）
 STATE_CODE VARCHAR2（2）
 TIMES_PURCHASED NUMBER（3） 

サンプルはどこですか

cust_id、state_code、times_purchasedを選択します
顧客から
cust_id による注文。

顧客の識別子、状態コード、および彼が何かを購入した回数を示します。

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

各州ごとにグループ化された顧客数と注文数を調べる必要があります。

state_code、times_purchased、count（1）cntを選択します
顧客から
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
 ... 

このクエリは必要なものを返しますが、次の形式の方がはるかに便利です。

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

バージョン11gより前では、これはsum（decode（state_code、 'CT'、1,0）“ CT”、sum（decode（state_code、 'NY'、1,0）“ NY”、...の繰り返しにより繰り返し実行する必要がありましたが、ピボット関数のおかげです。これを簡単に行うことができます。

選択*から（
「購入頻度」、state_codeとしてtimes_purchasedを選択します
顧客から
）ピボット（
カウント（state_code）
state_code in（「NY」は「New York」、「CT」は「Connecticut」、「NJ」は「New Jersey」、「FL」は「Florida」、「MO」は「Missouri」）
）
1で注文する
/

  Puchase Frequencyニューヨークコネチカットニュージャージーフロリダミズーリ
 ----------------- ---------- ----------- ----------- -------- ----------
 0 16 601 90 0 0 0
 1 33048 165 0 0 0
 2 33151179 0 0 0
 3 32 978 173 0 0 0
 4 33109173 0 1 0
 ... 

Unpivot関数はその逆を行います。

まだ11gに移行していない人のために、修正したTom Kiteコードを提供できます 。

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;
/



使用例：

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 .

結果：