Oracle 11 g - PIVOT/UNPIVOT(Транспонирование)

Pivot_clause позволяет транспортировать строки в столбцы,то есть раньше приходилось писать для каждого столбца отдельный декод:

sum(decode(id,1,val)) 

или его аналог вида :

sum(case when id=1 then val else 0 end). 

Синтаксис оператора:

SELECT ....
FROM table
   PIVOT
     (
      aggregate-function(column)
      FOR pivot_column IN (value1, value2,..., valuen)
        ) 
WHERE .....

Где table название нашей таблицы или любое вложенное представление,aggregate-function(column) - колонка над которой производим агрегацию, pivot_column - для кого производим агрегацию,
value1, value2,..., valuen - значения, для которого будет производится расчет.


Рассмотрим пример:

create table pivot_test as 
select 290 region_id,1 id,3000 val from dual union all
select 290,1,9884 from dual union all
select 290,1,4534 from dual union all
select 290,2,1234 from dual union all
select 290,2,9745 from dual union all
select 290,3,4324 from dual union all
select 290,3,3212 from dual union all
select 290,4,9000 from dual;

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

select region_id,
sum(decode(id,1,val)) as Moscow,
sum(decode(id,2,val)) as NY,
sum(decode(id,3,val)) as Cair,
sum(decode(id,4,val)) as Tokia
from pivot_test
group by region_id
order by 1;

Теперь используем Pivot_clause:

select * from (select region_id,id,val from  pivot_test)
pivot (sum(val) for id in (1 as Moscow,2 as NY, 3 as Cair, 4 as Tokia));

Pivot_clause вычисляет агрегат функций sum(val), указанных после оператора pivot, для каждого значения id указанного после оператора in .В Агрегирующих функциях необходимо указать предложение group by , чтобы возвращать несколько значений, но pivot_clause не содержит явного предложения group by. Вместо этого, pivot_clause выполняет неявный group by. неявной группировки на основе всех столбцов, не упомянутые в pivot_clause, наряду с множеством значений, указанных в pivot_in_clause.

Усложняем, нужно подсчитать для 1 и 2 вместе, для 3 и 4 также:

Классический метод :

select region_id,
sum(case when id in (1,2) then val end) "Moscow+NY",
sum(case when id in (3,4) then val end) "Cair+Tokia",
from pivot_test
group by region_id
order by 1;

Теперь опять используем Pivot_clause:

select * from (
select region_id,decode(id,2,1,1,1,3,3,4,3) id,val from pivot_test )
pivot (sum(val) for id in (1 as "Moscow+NY" ,3 as "Cair+Tokia") );

Еще один боевой пример:

1. Кусок отчета без PIVOT :

select street_name, house_nomer,
       count(distinct case when part_type=1 then flat_id_sys end),
       count(distinct case when part_type=2 then flat_id_sys end),
       count(distinct case when part_type=3 then flat_id_sys end),
       count(distinct case when part_type=4 then flat_id_sys end)
  from ao$addr a
  join ao$square s
    on a.addr_id = s.flat_addr
  join ao$flat_attr fa
    on s.flat_attr = fa.attr_id
   and house_id_sys = 5135
   and sd = '01.01.2011'
  group by street_name, house_nomer;

1. Кусок отчета c PIVOT :

select * from (
select distinct street_name,
 house_nomer,flat_nomer,part_type
  from ao$addr a
  join ao$square s
    on a.addr_id = s.flat_addr
  join ao$flat_attr fa
    on s.flat_attr = fa.attr_id
   and house_id_sys = 5135
   and sd = '01.01.2011' )
pivot (count(flat_nomer) for part_type in (1 as "t1",
                                           2 as "t2",
                                           3 as "t3",
                                           4 as "t4"));