Пример исходной таблицы:
Country | Goods | Revenue |
Ukraine | meat | 15000 |
Ukraine | beer | 8700 |
Russia | milk | 8000 |
Russia | bread | 7300 |
Poland | cheese | 11000 |
... |
Вид, к которому ее необходимо преобразовать. Т.е. кросс таблица в которой значения goods вынесены в названия столбцов:
Goods | Ukraine | Russia | Poland |
bread | 3500 | 7300 | 15200 |
beer | 17700 | 9000 | 18700 |
milk | 12000 | 16300 | 6000 |
meat | 45000 | 0 | 47500 |
cheese | 11000 | 23500 | 11000 |
Кросс таблица не является реляционной т.к. заголовки столбцов формируются из данных хранящихся в самой таблице, реляционной таблице. И хотя такое преобразование было востребовано, из-за этой «нереляционности» разработчики не спешили реализовывать поддержку такой возможности в своих СУБД. Оператор PIVOT появился, начиная только с MS SQL Server 2005 и Oracle 11g.
Без использования PIVOT
Преобразовать такую таблицу можно было преобразовать используя условие CASE (или DECODE)
select s.goods, sum(case when s.country = 'ukraine' then s.revenue else 0 end) as ukraine, sum(case when s.country = 'russia' then s.revenue else 0 end) as russia, sum(case when s.country = 'poland' then s.revenue else 0 end) as poland from sales s group by s.goods
Goods | Ukraine | Russia | Poland |
bread | 3500 | 7300 | 15200 |
beer | 17700 | 9000 | 18700 |
milk | 12000 | 16300 | 6000 |
meat | 45000 | 0 | 47500 |
cheese | 11000 | 23500 | 11000 |
Использование выражения PIVOT в запросе позволяет транспонировать строки в столбцы (PIVOT) или столбцы в строки (UNPIVOT, соответственно).
Синтаксис запроса:
SELECT … FROM … PIVOT ( pivot pivot for pivot in ) WHERE …Где:
pivot – столбцы по которым будет производится агрегация
pivot for – столбцы по которым будет производится группировка
pivot in – определяет названия столбцов в таблице в которые будут подставляться значения агрегатов.
Пример использования PIVOT
select * from sales PIVOT ( sum(REVENUE) for COUNTRY in ('ukraine' Ukraine, 'russia' Russia, 'poland' Poland))
Goods | Ukraine | Russia | Poland |
bread | 3500 | 7300 | 15200 |
beer | 17700 | 9000 | 18700 |
milk | 12000 | 16300 | 6000 |
meat | 45000 | 47500 | |
cheese | 11000 | 23500 | 11000 |
Дополнительные преобразования запроса
Если необходимы дополнительные преобразования данных до кросс запроса можно использовать вложенный запрос, либо выражение WITH.
В примерах ниже не используются дополнительные преобразования, а лишь указано где в общей конструкции запроса они могут быть реализованы.
Вложенный запрос
select * from (select * from sales -- преобразование данных, запрос) PIVOT (sum(REVENUE) for COUNTRY in ('ukraine' Ukraine, 'russia' Russia, 'poland' Poland ))Выражение WITH
with sl as (select * from sales -- преобразование данных, запрос) select * from sl PIVOT (sum(REVENUE) for COUNTRY in ('ukraine' Ukraine, 'russia' Russia, 'poland' Poland ))
Использование алиасов
Алиасы могут применятся к значением указанным в pivot in и к значениям агрегатов. Если задан алиас для агрегата, то он будет выведен в название столбца с использованием в качестве разделителя символа «_».
select * from sales PIVOT (sum(REVENUE) as revenue for COUNTRY in ('ukraine' Ukraine, 'russia' Russia, 'poland' Poland ))
Goods | Ukraine_revenue | Russia_revenue | Poland_revenue |
bread | 3500 | 7300 | 15200 |
beer | 17700 | 9000 | 18700 |
milk | 12000 | 16300 | 6000 |
meat | 45000 | 47500 | |
cheese | 11000 | 23500 | 11000 |
Использование нескольких агрегатов
Если используется несколько агрегатов, например, кроме дохода еще нужно посчитать кол-во магазинов в каждой из стран то они разделяются запятыми:
select * from sales PIVOT (sum(REVENUE) as revenue, count(SHOP_NAME) as shop_cnt for COUNTRY in ('ukraine' Ukraine, 'russia' Russia, 'poland' Poland ))В таком случае в результате будет по два столбца для каждой страны, с доходом и количеством магазинов. Отображаться они будут в том порядке в котором указаны в условии.
Группировка по нескольким столбцам
Если используется группировка не только по странам, а например и по городам. Можно использовать сравнение нескольких значений
select * from sales_points PIVOT (sum(REVENUE) as revenue for (COUNTRY, CITY) in (('ukraine', 'kiev') as Ukraine_Kiev, ('russia', 'moskow') as Russia_Moskow, ('poland', 'warsaw') as Poland_Warsaw))
XML
К сожалению, во всех рассмотренных вариантах названия для pivot столбцов задаются явно. Нельзя использовать ни вложенный запрос, ни динамически менять значения.
По отношению к рассматриваемой таблице это значит, что если будет не 3 страны, а 30 все их нужно перечислять в pivot in. А если у нас появятся продажи в других странах, их так же придется добавлять «вручную».
Выходом из такой ситуации может быть использование XML. Тогда запрос примет следующий вид:
select * from sales PIVOT XML (sum(REVENUE) for COUNTRY in (ANY))
UNPIVOT
Совершает обратное преобразование по отношению к PIVOT. Т.е. столбцы преобразовывает в строки.
Синтаксис аналогичен PIVOT
SELECT … FROM … UNPIVOT [INCLUDE|EXCLUDE NULLS] ( pivot pivot for pivot in ) WHERE …