PIVOT SQL Server

В этом учебном пособии вы узнаете, как использовать оператор PIVOT в SQL Server (Transact-SQL) с синтаксисом и примерами.

Описание

Оператор PIVOT SQL Server (Transact-SQL) позволяет писать кросс-табуляцию. Это означает, что вы можете агрегировать свои результаты и поворачивать строки в столбцы.

Синтаксис

Синтаксис предложения PIVOT в SQL Server (Transact-SQL):

SELECT first_column AS ,
[pivot_value1], [pivot_value2], … [pivot_value_n]
FROM
() ASPIVOT
(
aggregate_function()
FOR IN ([pivot_value1], [pivot_value2], … [pivot_value_n])
) AS ;

Параметры или аргументы

first_column — столбец или выражение, которое будет отображаться в качестве первого столбца в сводной таблице.
first_column_alias — заголовок столбца для первого столбца в сводной таблице.
pivot_value1, pivot_value2, … pivot_value_n — список значений для поворота.
source_table — оператор SELECT, который предоставляет исходные данные для сводной таблицы.
source_table_alias — псевдоним для source_table.
aggregate_function — агрегирующая функция, такая как SUM, COUNT, MIN, MAX или AVG.
aggregate_column — столбец или выражение, которое будет использоваться с aggregate_function.
pivot_column — столбец, содержащий значения поворота.
pivot_table_alias — псевдоним для сводной таблицы.

Применение

PIVOT может использоваться в следующих версиях SQL Server (Transact-SQL):
SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Запросы создания таблиц и данные для примеров:

Выполните скрипты для создания таблиц и для заполнения данными. Затем попробуйте примеры в вашей собственной базе данных!

Пример

Оператор PIVOT может использоваться в SQL Server (Transact-SQL).
Рассмотрим пример. Если бы у нас была таблица employees, которая содержала следующие данные:

employee_number last_name first_name salary dept_id
14012 Зубова Рената 54000 45
28954 Маркин Тибул 80000 45
31957 Скрепкин Григорий 42000 45
41312 Платов Роман 57500 30
64029 Корнеев Иван 65000 30

И мы запустили следующий оператор SQL, который создает кросс-табулирующий запрос с использованием предложения PIVOT:

Это вернет следующий результат:

TotalSalaryByDept 30 45
TotalSalary 122500 176000

В этом примере создается сводная таблица для отображения TotalSalary (общей зарплаты) для dept_id 30 и dept_id 45. Результаты отображаются в одной строке, причем два dept_id (отдела) отображаются в каждом столбце.

Теперь давайте разобъем предложение PIVOT и объясним, как это работает.

Определение столбцов в результатах кросс-табуляции
Во-первых, мы хотим указать, какие поля включать в результаты кросс-табуляции. В этом примере мы хотим включить литеральное значение «TotalSalary» в качестве первого столбца в сводной таблице. И мы хотим создать один столбец для dept_id 30 и второй столбец для dept_id 45. Это дает нам 3 столбца в нашей сводной таблице.

Укажем данные исходной таблицы

Затем нам нужно указать оператор SELECT, который вернет исходные данные для сводной таблицы.
В этом примере мы хотим вернуть значения dept_id и salary из таблицы employees:

Вы должны указать псевдоним для исходного запроса. В этом примере мы использовали псевдоним запроса как SourceTable.

Указать агрегирующую функцию

Затем нам нужно указать, какую агрегирующую функцию использовать при создании нашего запроса для кросс — табуляции. Вы можете использовать любую агрегирующую функцию, такую как функции SUM, COUNT, MIN, MAX или AVG.

В этом примере мы будем использовать функцию SUM. Это суммирует значения salary:

Указать Pivot значеня

Наконец, нам нужно указать, какие pivot значения включать в наши результаты. Они будут использоваться в качестве заголовков столбцов в нашем запросе кросс-табуляции.

В этом примере мы вернем только значения dept_id 30 и 45. Эти значения станут заголовками столбцов в нашей pivot таблице. Также обратите внимание, что эти значения представляют собой конечный список значений dept_id и не обязательно будут содержать все возможные значения.

Теперь, когда мы собираем все это вместе, мы получаем следующую pivot таблицу:

TotalSalaryByDept 30 45
TotalSalary 122500 176000