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

Изменения: 17 июля 2006 г.

Реляционные операторы PIVOT и UNPIVOT можно использовать для изменения возвращающего табличное значение выражения в другой таблице. Оператор PIVOT разворачивает возвращающее табличное значение выражение, преобразуя уникальные значения одного столбца выражения в несколько выходных столбцов, а также, в случае необходимости, объединяет оставшиеся повторяющиеся значения столбца и отображает их в выходных данных. Оператор UNPIVOT производит действия, обратные PIVOT, преобразуя столбцы возвращающего табличное значение выражения в значения столбца.

ms177410.note(ru-ru,SQL.90).gifПримечание.
При применении 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 не учитываются.

ms177410.note(ru-ru,SQL.90).gifВажно!
При статистической обработке данных с использованием функций, содержащих оператор 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 г.

Добавления
  • Добавлено дополнительное описание синтаксиса оператора PIVOT.
  • Добавлен пример базового синтаксиса оператора PIVOT.