Новое в стандарте и реализациях языка SQL

Операторы PIVOT и UNPIVOT

Давайте рассмотрим такую задачу.

Для каждого производителя из таблицы Product определить число моделей каждого типа продукции.

Задачу можно решить стандартными средствами с использованием оператора 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

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

    Схема

Теперь решение через PIVOT:

Консоль

Выполнить

SELECT maker, -- столбец (столбцы), значения из которого формируют заголовки строк
[pc], [laptop], [printer] -- значения из столбца, который указан в предложении type, 
-- формирующие заголовки столбцов 
FROM Product -- здесь может быть подзапрос
PIVOT -- формирование пивот-таблицы
(COUNT(model) -- агрегатная функция, формирующая содержимое сводной таблицы
FOR type -- указывается столбец, 
-- уникальные значения в котором будут являться заголовками столбцов
IN([pc], [laptop], [printer]) --указываются конкретные значения в столбце type, 
 -- которые следует использовать в качестве заголовков, 
 -- т.к. нам могут потребоваться не все
) pvt -- алиас для сводной таблицы

SELECT maker, -- столбец (столбцы), значения из которого формируют заголовки строк [pc], [laptop], [printer] -- значения из столбца, который указан в предложении type, -- формирующие заголовки столбцов FROM Product -- здесь может быть подзапрос PIVOT -- формирование пивот-таблицы (COUNT(model) -- агрегатная функция, формирующая содержимое сводной таблицы FOR type -- указывается столбец, -- уникальные значения в котором будут являться заголовками столбцов IN([pc], [laptop], [printer]) --указываются конкретные значения в столбце type, -- которые следует использовать в качестве заголовков, -- т.к. нам могут потребоваться не все ) pvt -- алиас для сводной таблицы

    Схема

Надеюсь, что комментарии к коду достаточно понятны для того, чтобы написать оператор PIVOT без шпаргалки. Давайте попробуем.

Пример 2.

Посчитать среднюю цену на ноутбуки в зависимости от размера экрана.

Задача элементарная и решается с помощью группировки:

Консоль

Выполнить

SELECT screen, AVG(price) avg_ 
FROM Laptop 
GROUP BY screen

SELECT screen, AVG(price) avg_ FROM Laptop GROUP BY screen

    Схема

А вот как можно повернуть эту таблицу с помощью 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

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

    Схема

В отличие от сводных таблиц, в операторе PIVOT требуется явно перечислить столбцы для вывода. Это серьезное ограничение, т.к. для этого нужно знать характер данных, а значит и применять в приложениях этот оператор мы сможем, как правило, только к справочникам (вернее, к данным, которые берутся из справочников).

Если рассмотренных примеров покажется недостаточно, чтобы понять и использовать без затруднений этот оператор, я вернусь к нему, когда придумаю нетривиальные примеры, где использование оператора PIVOT позволяет существенно упростить код.

Я написал этот опус в помощь тем, кому оператор PIVOT интуитивно непонятен. Могу согласиться с тем, что в реляционном языке SQL он выглядит инородным телом. Собственно, иначе и быть не может ввиду того, что поворот (транспонирование) таблицы является не реляционной операцией, а операцией работы с многомерными структурами данных.