Функции 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, могу предложить свой модифицированный код Тома Кайта: https://github.com/xtender/XT_PIVOT

Пример использования:

begin
 :qq:=xt_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 :=xt_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.

Результат: