Поделиться через

  • Статья

Использование операторов 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 <несведенный столбец>,

    [первый сведенный столбец] AS <имя столбца>,

    [второй сведенный столбец] AS <имя столбца>,

    ...

    [последний сведенный столбец] AS <имя столбца>,

FROM

    (< запрос SELECT, извлекающий эти данные>)

    AS <псевдоним исходного запроса>

PIVOT

(

    <статистическая функция>(<статистически обработанный столбец>)

FOR

[<столбец, содержащий значения, которые станут именами столбцов>]

    IN ( [первый сведенный столбец], [второй сведенный столбец],

    ... [последний сведенный столбец])

)AS <псевдоним сведенной таблицы>

<необязательное предложение ORDER BY>;

Базовый пример PIVOT

В следующем примере кода создается таблица, включающая два столбца и четыре строки.

USE AdventureWorks2008R2 ;
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 образца базы данных AdventureWorks2008R2 для определения количества заказов на покупку, размещенных некоторым сотрудником. Требуемые данные, отсортированные по поставщикам, можно извлечь при выполнении следующего запроса.

USE AdventureWorks2008R2;
GO
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM 
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;

Здесь приводится частичный результирующий набор.

VendorID    Emp1        Emp2        Emp3        Emp4        Emp5

1492        2           5           4           4           4

1494        2           5           4           5           4

1496        2           4           4           5           5

1498        2           5           4           4           4

1500        3           4           4           5           4

Данные, возвращаемые в результате выполнения указанного подзапроса выборки, сводятся в столбец 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 образца базы данных База данных AdventureWorks2008R2 оператор PIVOT используется для определения полного объема продаж каждого продавца в течение каждого финансового года. Чтобы создать скрипт для представления в среде Среда SQL Server Management Studio, найдите в обозревателе объектов это представление в папке Представления базы данных База данных AdventureWorks2008R2. Щелкните правой кнопкой мыши имя представления и выберите Создать скрипт для представления.

См. также

Справочник

FROM (Transact-SQL)

Выражение CASE (Transact-SQL)