Оператор PIVOT
Давайте рассмотрим такую задачу.
Пример 1.
Задачу можно решить стандартными средствами с использованием оператора CASE:
SELECT maker,
SUM(CASE type WHEN 'pc' THEN 1 ELSE 0 END) PC
, SUM(CASE type WHEN 'laptop' THEN 1 ELSE 0 END) Laptop
, SUM(CASE type WHEN 'printer' THEN 1 ELSE 0 END) Printer
FROM Product
GROUP BY maker;
[[ column ]] |
---|
[[ value ]] |
Теперь решение через PIVOT:
SELECT maker, -- столбец (столбцы), значения из которого формируют заголовки строк
[pc], [laptop], [printer] -- значения из столбца, который указан в предложении type,
-- формирующие заголовки столбцов
FROM Product -- здесь может быть подзапрос
PIVOT -- формирование пивот-таблицы
(COUNT(model) -- агрегатная функция, формирующая содержимое сводной таблицы
FOR type -- указывается столбец,
-- уникальные значения в котором будут являться заголовками столбцов
IN([pc], [laptop], [printer]) --указываются конкретные значения в столбце type,
-- которые следует использовать в качестве заголовков,
-- т.к. нам могут потребоваться не все
) pvt ;-- алиас для сводной таблицы
[[ column ]] |
---|
[[ value ]] |
Надеюсь, что комментарии к коду достаточно понятны для того, чтобы написать оператор PIVOT без шпаргалки. Давайте попробуем.
Пример 2.
Задача элементарная и решается с помощью группировки:
SELECT screen, AVG(price) avg_
FROM Laptop
GROUP BY screen;
[[ column ]] |
---|
[[ value ]] |
screen | avg_ |
---|---|
11 | 700 |
12 | 960 |
14 | 1175 |
15 | 1050 |
А вот как можно повернуть эту таблицу с помощью PIVOT:
SELECT [avg_],
[11],[12],[14],[15]
FROM (SELECT 'average price' AS 'avg_', screen, price FROM Laptop) x
PIVOT
(AVG(price)
FOR screen
IN([11],[12],[14],[15])
) pvt;
[[ column ]] |
---|
[[ value ]] |
avg_ | 11 | 12 | 14 | 15 |
---|---|---|---|---|
average price | 700 | 960 | 1175 | 1050 |
В отличие от сводных таблиц, в операторе PIVOT требуется явно перечислить столбцы для вывода. Это серьезное ограничение, т.к. для этого нужно знать характер данных, а значит и применять в приложениях этот оператор мы сможем, как правило, только к справочникам (вернее, к данным, которые берутся из справочников).
Если рассмотренных примеров покажется недостаточно, чтобы понять и использовать без затруднений этот оператор, я вернусь к нему, когда придумаю нетривиальные примеры, где использование оператора PIVOT позволяет существенно упростить код.
Я написал этот опус в помощь тем, кому оператор PIVOT интуитивно непонятен. Могу согласиться с тем, что в реляционном языке SQL он выглядит инородным телом. Собственно, иначе и быть не может ввиду того, что поворот (транспонирование) таблицы является не реляционной операцией, а операцией работы с многомерными структурами данных.