Использование операторов PIVOT и UNPIVOT
Изменения: 17 июля 2006 г.
Реляционные операторы PIVOT и UNPIVOT можно использовать для изменения возвращающего табличное значение выражения в другой таблице. Оператор PIVOT разворачивает возвращающее табличное значение выражение, преобразуя уникальные значения одного столбца выражения в несколько выходных столбцов, а также, в случае необходимости, объединяет оставшиеся повторяющиеся значения столбца и отображает их в выходных данных. Оператор UNPIVOT производит действия, обратные PIVOT, преобразуя столбцы возвращающего табличное значение выражения в значения столбца.
Примечание. |
---|
При применении UNPIVOT к базам данных, обновленным до версии Microsoft SQL Server 2005, уровень совместимости базы данных должен иметь значение 90. Сведения об установке уровня совместимости баз данных см. в разделе sp_dbcmptlevel (Transact-SQL). |
Синтаксис оператора PIVOT является более простым и понятным, чем синтаксис, который может выполнить то же действие с помощью последовательности инструкций SELECT...CASE. Полное описание синтаксиса инструкции PIVOT см. в разделе FROM (Transact-SQL).
Аннотированный синтаксис инструкции PIVOT показан ниже.
SELECT
<несведенный столбец> ,
[первый сведенный столбец] AS
<имя_столбца> ,
[второй сведенный столбец] AS <имя столбца> ,
...
[последний сведенный столбец] AS
<имя столбца>
FROM
(
<запрос SELECT, который формирует данные> )
AS
<псевдоним исходного запроса>
PIVOT
(
<aggregation function>(
<агрегированный столбец> )
FOR
[<столбец, который содержит значение, которые станут заголовками столбцов>]
IN``(
[первый сведенный столбец] ,
[второй сведенный столбец] ,
...
[последний сведенный столбец] )
)``AS
<псевдоним сводной таблицы>
<необязательное предложение ORDER BY>
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 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
. The 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. Щелкните правой кнопкой мыши имя представления и выберите Создать сценарий для представления.
См. также
Другие ресурсы
FROM (Transact-SQL)
CASE (Transact-SQL)
Справка и поддержка
Получение помощи по SQL Server 2005
Журнал изменений
Версия | Журнал |
---|---|
17 июля 2006 г. |
|