Оператор PIVOT

По материалам статьи Craig Freedman: The PIVOT Operator

Несколько статей будут посвящены тому как в SQL Server реализован оператор PIVOT и UNPIVOT. Начнем с оператора PIVOT. Оператор PIVOT берет нормализованную таблицу и преобразует ее в другой вид, в котором столбцы результирующей таблицы получаются из значений исходной таблицы. Например, предположим, что мы хотим хранить данные о суммарной выручке от продаж за год по каждому из сотрудников. Для этих целей можно создать следующую таблицу:

CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
INSERT Sales VALUES(1, 2005, 12000)
INSERT Sales VALUES(1, 2006, 18000)
INSERT Sales VALUES(1, 2007, 25000)
INSERT Sales VALUES(2, 2005, 15000)
INSERT Sales VALUES(2, 2006, 6000)
INSERT Sales VALUES(3, 2006, 20000)
INSERT Sales VALUES(3, 2007, 24000)

Обратите внимание, что в этой таблице на одного сотрудника приходится одна строка на каждый год. Кроме того, сотрудники 2 и 3 имеют данные о продажах только за два года из трех. Теперь предположим, что мы хотим показать эти данные в табличном виде с одной строкой на каждого сотрудника и данными о продажах за все три года в этой строке. Мы можем очень легко добиться этого, используя оператор преобразования PIVOT:

SELECT EmpId, [2005], [2006], [2007]
FROM (SELECT EmpId, Yr, Sales FROM Sales) AS s
PIVOT (SUM(Sales) FOR Yr IN ([2005], [2006], [2007])) AS p

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

EmpId       2005                  2006                  2007
----------- --------------------- --------------------- ---------------------
1           12000.00              18000.00              25000.00
2           15000.00              6000.00               NULL
3           NULL                  20000.00              24000.00

Обратите внимание, что SQL Server выводит NULL для отсутствующих данных о продажах сотрудников 2 и 3.

Ключевое слово SUM (или либо другой агрегат) является обязательным. Если таблица «Sales» содержит для сотрудника за какой-то год несколько строк, PIVOT в результате объединяет их (в данном случае путем суммирования) в одну строку данных. Разумеется, поскольку в этом примере запись в каждой «ячейке» выборки является результатом суммирования одной строки, мы также легко могли бы использовать и другой агрегат, например, MIN или MAX. Я использовал SUM, поскольку он более интуитивно понятен.

Этот пример с PIVOT является обратимым. Информацию из выборки можно использовать для восстановления исходной таблицы с помощью оператора UNPIVOT (о котором я расскажу в следующей статье). Однако не все операции с PIVOT являются обратимыми. Чтобы быть обратимой, операция с PIVOT должна соответствовать следующим критериям:

• Все входные данные должны подпадать под преобразование. Если будет использоваться какой-либо фильтр, в том числе предложение IN, некоторые данные могут быть исключены из результата PIVOT. Например, если бы мы в приведенном выше примере выбирали данные о продажах только за 2006 и 2007 годы, очевидно, что мы не смогли бы восстановить из выборки данные о продажах за 2005 год.

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

• Агрегатная функция должна быть реверсивной (при использовании одной строки на входе). SUM, MIN, MAX и AVG возвращают одно, полученное из таблицы на входе значение без изменений и, таким образом, могут быть реверсированы. COUNT не возвращает свое входное значение без изменений и, следовательно, не может быть обратимо.

Ниже представлен пример необратимой операции PIVOT. В нём рассчитывается общий объем продаж для всех сотрудников за все три года, но результат не детализируется по сотруднику.

SELECT [2005], [2006], [2007]
FROM (SELECT Yr, Sales FROM Sales) AS s
PIVOT (SUM(Sales) FOR Yr IN ([2005], [2006], [2007])) AS p

Результат исполнения запроса показан ниже. Каждая ячейка представляет собой сумму двух или трех строк таблицы на входе.

2005                  2006                  2007
--------------------- --------------------- ---------------------
27000.00              44000.00              49000.00