Использование операторов PIVOT и UNPIVOT
Реляционные операторы PIVOT и UNPIVOT можно использовать для изменения возвращающего табличное значение выражения в другой таблице. Оператор PIVOT разворачивает возвращающее табличное значение выражение, преобразуя уникальные значения одного столбца выражения в несколько выходных столбцов, а также, в случае необходимости, объединяет оставшиеся повторяющиеся значения столбца и отображает их в выходных данных. Оператор UNPIVOT производит действия, обратные PIVOT, преобразуя столбцы возвращающего табличное значение выражения в значения столбца.
Примечание |
---|
Если инструкции PIVOT и UNPIVOT применяются к базам данных, обновленным до версии SQL Server 2005, уровень совместимости базы данных должен быть не ниже 90. Сведения об установке уровня совместимости баз данных см. в разделе sp_dbcmptlevel (Transact-SQL). |
Синтаксис оператора PIVOT является более простым и понятным, чем синтаксис, который может выполнить то же действие с помощью последовательности инструкций SELECT...CASE. Полное описание синтаксиса инструкции PIVOT см. в разделе Предложение FROM (Transact-SQL).
Аннотированный синтаксис инструкции PIVOT показан ниже.
SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;
Базовый пример PIVOT
В следующем примере создается таблица, включающая два столбца и четыре строки.
USE AdventureWorks ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
Ниже приводится результирующий набор.
DaysToManufacture AverageCost
0 5.0885
1 223.88
2 359.1082
4 949.4105
Для значения DaysToManufacture, равного трем, продукты не определены.
Следующий код отображает тот же самый результат, сведенный так, что значения DaysToManufacture становятся заголовками. Для значения трех [3] дней приводится столбец, даже если результат равен NULL.
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;
Ниже приводится результирующий набор.
Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
Сложный пример PIVOT
Обычно оператор PIVOT может быть полезен при создании отчетов с перекрестными ссылками для сведения данных. Например, пусть необходимо обратиться к таблице PurchaseOrderHeader образца базы данных AdventureWorks для определения количества заказов на покупку, размещенных некоторым сотрудником. Требуемые данные, отсортированные по поставщикам, можно извлечь при выполнении следующего запроса.
USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY pvt.VendorID;
Здесь приводится частичный результирующий набор.
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
1 4 3 5 4 4
2 4 1 5 5 5
3 4 3 5 4 4
4 4 2 5 5 4
5 5 1 5 5 5
Данные, возвращаемые в результате выполнения указанного подзапроса выборки, сводятся в столбец EmployeeID.
SELECT PurchaseOrderID, EmployeeID, VendorID
FROM PurchaseOrderHeader;
Это означает, что уникальные значения столбца EmployeeID становятся полями итогового результирующего набора. Следовательно, существует столбец для каждого числа EmployeeID, указанного в предложении сведения: в этом случае сотрудники 164, 198, 223, 231 и 233. PurchaseOrderID служит столбцом значений, по которому группируются столбцы, возвращаемые в конечный вывод и называемые столбцами группирования. В этом случае значения столбцов группирования обрабатываются с помощью функции COUNT. Обратите внимание, что при вычислении функции COUNT для каждого сотрудника выдается предупреждение, сообщающее, что пустые значения столбца PurchaseOrderID не учитываются.
Важно! |
---|
При статистической обработке данных с использованием функций, содержащих оператор PIVOT, пустые значения столбцов не учитываются. |
Оператор UNPIVOT выполняет действия, обратные оператору PIVOT, преобразуя столбцы данных в строки. Допустим, что таблица, созданная в ходе выполнения предыдущего примера, хранится в базе данных и имеет идентификатор pvt. Пусть необходимо преобразовать идентификаторы столбцов Emp1, Emp2, Emp3, Emp4 и Emp5 в строки данных, сгруппированные по поставщикам. В этом случае необходимо определить два дополнительных столбца. Присвоим столбцу, содержащему преобразуемые значения (Emp1, Emp2,...), имя Employee, а столбцу, содержащему значения поворачиваемых столбцов, имя Orders. Указанные столбцы связаны соответственно с такими параметрами в определении на языке Transact-SQL, как pivot_column и value_column. Запрос:
--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO
Здесь приводится частичный результирующий набор.
VendorID Employee Orders
1 Emp1 4
1 Emp2 3
1 Emp3 5
1 Emp4 4
1 Emp5 4
2 Emp1 4
2 Emp2 1
2 Emp3 5
2 Emp4 5
2 Emp5 5
...
Обратите внимание, что оператор UNPIVOT не является в точности обратным оператору PIVOT. Оператор PIVOT производит статистическую обработку и слияние нескольких строк в единую выходную строку. Оператор UNPIVOT не восстанавливает исходные возвращающие табличное значение выражения, так как строки были объединены. Оператор UNPIVOT удаляет пустые значения из обрабатываемых им данных. Поэтому в случае наличия в исходных столбцах пустых значений данные на выходе оператора UNPIVOT могут отличаться от данных до их обработки с помощью оператора PIVOT.
В представлении Sales.vSalesPersonSalesByFiscalYears образца базы данных AdventureWorks оператор PIVOT используется для определения полного объема продаж каждого продавца в течение каждого финансового года. Чтобы просмотреть сценарий представления в среде SQL Server Management Studio, в Обозревателе объектов найдите представление в папке Представления базы данных AdventureWorks. Щелкните правой кнопкой мыши имя представления и выберите Создать сценарий для представления.