Функции Oracle 11g Pivot, Unpivot

В версии 11g появились функции Pivot/Unpivot(которые сначала появились в MS SQL 2005), позволяющие динамически разносить вертикальные данные по столбцам как нам удобно.



Допустим у вас есть таблица customers:

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

Где выборка

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

показывает идентификатор заказчика, код штата, и сколько раз он что-либо покупал:

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

Нам нужно узнать количество заказчиков сгрупированных по каждому штату и по количеству их заказов:

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
...

Этот запрос выдает то, что нам нужно, но гораздо удобнее был бы в таком виде:

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»,… Но благодаря функции pivot мы можем это сделать просто:

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
...

Функция Unpivot совершает противоположные преобразования.

Тем же, кто еще не мигрировал на 11g, могу предложить свой модифицированный код Тома Кайта:

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.

Результат: