"Строку в столбец" и наоборот. Транспонирование таблиц. PIVOT и UNPIVOT. В т.ч. для нескольких полей

Далее в тексте есть обновление статьи для нескольких полей

До версии 11 задача транспонирования решалась довольно сложно.

В версии 11 в синтаксисе оператора SELECTпоявились фразы PIVOT и UNPIVOT , которые выполняют такое транспонирование прозрачно и просто.
План запроса получается простой и эффективный -- запрос будет хорошо выполняться даже на больших объёмах. Прежние варианты с самосоединением таблиц были бы менее эффективны

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

Имеем таблицу курсов валют к рублю:

ISO_CODE	RATE_DATE	RATE_VALUE
EUR	01.02.2012	39,9678
EUR	02.02.2012	39,6777
EUR	03.02.2012	39,7664
UAH	01.02.2012	3,7731
UAH	02.02.2012	3,7881
UAH	03.02.2012	3,7547
USD	01.02.2012	30,3131
USD	02.02.2012	30,4067
USD	03.02.2012	30,1855

Хотим получить такую таблицу

RATE_DATE	RATE_EUR	RATE_UAH	RATE_USD
01.02.2012	39,9678	3,7731	30,3131
02.02.2012	39,6777	3,7881	30,4067
03.02.2012	39,7664	3,7547	30,1855

В 11g это можно сделать таким запросом:

select * from (select ISO_CODE, RATE_DATE, RATE_VALUE from EXCH_RATES)

pivot (max (RATE_VALUE)
       for ISO_CODE in ('EUR' as RATE_EUR, 'UAH' as RATE_UAH, 'USD' as RATE_USD)
      )




Комментарии:

1. Первый select * from ( ) обязателен

2. Внутри фразы pivot (  ) сначала должна быть агрегатная функция, даже если фраза group by явно не указана. PIVOT выполнит неявный group by по всем полям, кроме поля в агрегатной функции и полей после FOR. Это необходимо, чтобы обработать ситуацию, когда там окажется несколько строк. Если строка одна -- не важно, что писать, MIN, MAX, SUM, AVG...

План:

Синтаксис описан в доке по SELECT
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm
Искать pivot_clause

UNPIVOT выполняет обратное преобрзование

UPDATE 15.06.2015
Как транспонировать несколько полей ?


В приведённом выше примере мы транспонировали только одно поле -- RATE_VALUE превратилось в RATE_EUR, RATE_UAH, RATE_USD

Но бывают задачи, когда нужно транспонировать несколько полей, ниже я привожу пример, связанный тоже с курсами валют, но курсы взяты с рынка Форекс, и там строка курса содержит несколько значений OPEN (значение на момент начала интервала), MIN (минимальное, которое достигалось в интервале), MAX (максимальное, которое достигалось в интервале), CLOSE (каким значением курса интервал закончился), VOLUME (объём операций)

В этом случае, названия колонок нам нужно собрать перемножением множества значений ('OPEN', 'MIN', 'MAX', 'CLOSE', 'VOLUME') на множество значений названий пар валют

Этот запрос можно написать так, что в каждой колонке сначала будет название пары валют (поле из FOR) и через подчёркивание название поля, которое мы транспонируем

Сначала приведу запрос

select * from (select * from RATES)
pivot (avg (QOPEN)   as QOPEN,
       avg (QMIN)    as QMIN,
       avg (QMAX)    as QMAX,
       avg (QCLOSE)  as QCLOSE,
       avg (QVOLUME) as QVOLUME
       
       for (CURPAIR) in ('EURGBP' as EURGBP
                       , 'EURPLN' as EURPLN
                       , 'EURUSD' as EURUSD)
      )
order by 1

Алиасы полей, помеченные зелёным фоном -- обязательны

Вот таблица для тестирования

create table RATES (
    CURPAIR     varchar2(16) not null,
    SNAPDATE    date not null,
    QOPEN       number  not null,
    QMAX        number  not null,
    QMIN        number  not null,
    QCLOSE      number  not null,
    QVOLUME     number  not null);

И её заполнение

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURGBP', to_date ('2015.05.14 00:00', 'YYYY.MM.DD HH24:MI') ,0.7208,0.721,0.7208,0.721,5      );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURGBP', to_date ('2015.05.14 00:01', 'YYYY.MM.DD HH24:MI') ,0.7209,0.721,0.7209,0.721,4      );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURGBP', to_date ('2015.05.14 00:02', 'YYYY.MM.DD HH24:MI') ,0.7211,0.7211,0.7209,0.721,8     );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURGBP', to_date ('2015.05.14 00:03', 'YYYY.MM.DD HH24:MI') ,0.7209,0.721,0.7209,0.7209,5     );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURGBP', to_date ('2015.05.14 00:04', 'YYYY.MM.DD HH24:MI') ,0.721,0.721,0.7209,0.7209,2      );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURGBP', to_date ('2015.05.14 00:05', 'YYYY.MM.DD HH24:MI') ,0.721,0.721,0.721,0.721,1        );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURGBP', to_date ('2015.05.14 00:06', 'YYYY.MM.DD HH24:MI') ,0.7209,0.721,0.7209,0.7209,5     );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURGBP', to_date ('2015.05.14 00:07', 'YYYY.MM.DD HH24:MI') ,0.721,0.721,0.7209,0.7209,14     );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURGBP', to_date ('2015.05.14 00:08', 'YYYY.MM.DD HH24:MI') ,0.721,0.721,0.7209,0.7209,4      );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:00', 'YYYY.MM.DD HH24:MI') ,4.0937,4.0947,4.0937,4.0947,2    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:01', 'YYYY.MM.DD HH24:MI') ,4.0946,4.0946,4.0946,4.0946,1    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:02', 'YYYY.MM.DD HH24:MI') ,4.0947,4.0954,4.0947,4.0954,5    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:03', 'YYYY.MM.DD HH24:MI') ,4.0966,4.0966,4.0966,4.0966,3    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:04', 'YYYY.MM.DD HH24:MI') ,4.0955,4.0993,4.0955,4.0961,11   );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:05', 'YYYY.MM.DD HH24:MI') ,4.0964,4.0973,4.0961,4.0973,7    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:06', 'YYYY.MM.DD HH24:MI') ,4.0974,4.0976,4.0974,4.0976,2    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:07', 'YYYY.MM.DD HH24:MI') ,4.0974,4.0974,4.0973,4.0973,2    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:08', 'YYYY.MM.DD HH24:MI') ,4.0974,4.0974,4.0973,4.0973,2    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURPLN', to_date ('2015.05.14 00:09', 'YYYY.MM.DD HH24:MI') ,4.0974,4.0974,4.0974,4.0974,1    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURUSD', to_date ('2015.05.14 00:00', 'YYYY.MM.DD HH24:MI') ,1.1352,1.1354,1.1351,1.1353,6    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURUSD', to_date ('2015.05.14 00:01', 'YYYY.MM.DD HH24:MI') ,1.1354,1.1355,1.1353,1.1355,14   );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURUSD', to_date ('2015.05.14 00:02', 'YYYY.MM.DD HH24:MI') ,1.1354,1.1355,1.1354,1.1355,8    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURUSD', to_date ('2015.05.14 00:03', 'YYYY.MM.DD HH24:MI') ,1.1354,1.1355,1.135,1.1351,9     );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURUSD', to_date ('2015.05.14 00:05', 'YYYY.MM.DD HH24:MI') ,1.1352,1.1352,1.1352,1.1352,1    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURUSD', to_date ('2015.05.14 00:06', 'YYYY.MM.DD HH24:MI') ,1.1351,1.1352,1.1351,1.1351,3    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURUSD', to_date ('2015.05.14 00:07', 'YYYY.MM.DD HH24:MI') ,1.1352,1.1352,1.1351,1.1352,3    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURUSD', to_date ('2015.05.14 00:08', 'YYYY.MM.DD HH24:MI') ,1.1351,1.1352,1.1351,1.1351,3    );

insert into RATES (CURPAIR, SNAPDATE, QOPEN, QMAX, QMIN, QCLOSE, QVOLUME) values ('EURUSD', to_date ('2015.05.14 00:09', 'YYYY.MM.DD HH24:MI') ,1.135,1.1351,1.135,1.1351,4      );

commit;