FROM — использование PIVOT и UNPIVOT

Применимо к:SQL ServerAzure SQL DatabaseAzure Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)

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

Синтаксис для 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>;  

Замечания

Идентификаторы столбцов в предложении UNPIVOT следуют параметрам сортировки каталога. Для базы данных SQL параметры сортировки всегда SQL_Latin1_General_CP1_CI_ASсовпадают. Для частично содержащихся баз данных SQL Server параметры сортировки всегда Latin1_General_100_CI_AS_KS_WS_SCсовпадают. Если столбец используется в сочетании с другими столбцами, для предотвращения конфликтов требуется предложение collate (COLLATE DATABASE_DEFAULT).

В пулах Microsoft Fabric и Azure Synapse Analytics запросы с оператором PIVOT завершаются ошибкой, если в выходных данных столбца, не относящихся к сводной таблице, выполняется сбой. В качестве обходного решения удалите неключительный столбец из GROUP BY. Результаты запроса совпадают, так как это предложение GROUP BY дублируется.

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

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

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

USE AdventureWorks2022;  
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, указанному в предложении PIVOT: в данном случае это сотрудники 250, 251, 256, 257 и 260. PurchaseOrderID служит столбцом значений, по которому группируются столбцы, возвращаемые в конечный вывод и называемые столбцами группирования. В этом случае значения столбцов группирования обрабатываются с помощью функции COUNT. Обратите внимание, что при вычислении функции COUNT для каждого сотрудника появится предупреждающее сообщение о том, что значения NULL столбца PurchaseOrderID не учитываются.

Пример UNPIVOT

Оператор 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 удаляет значения NULL из обрабатываемых им данных. Поэтому в случае наличия в исходных столбцах значений NULL данные на выходе могут отличаться от данных до их обработки с помощью оператора PIVOT.

В представлении Sales.vSalesPersonSalesByFiscalYears образца базы данных AdventureWorks2022 предложение PIVOT используется для определения полного объема продаж каждого менеджера в течение каждого финансового года. Чтобы выполнить скрипт представления в СРЕДЕ SQL Server Management Studio, в обозревателе объектов найдите представление в папке Views для AdventureWorks2022 базы данных. Щелкните правой кнопкой мыши имя представления и выберите Создать скрипт для представления.

Далее

• FROM (Transact-SQL)
• CASE (Transact-SQL)