Использование операторов PIVOT и UNPIVOT

Базовый пример 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. Щелкните правой кнопкой мыши имя представления и выберите Создать сценарий для представления.

См. также

Справочник