FROM — использование PIVOT и UNPIVOT
Область применения:
SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure конечной точке аналитики платформы Аналитики Azure Synapse Analytics (PDW) в Microsoft Fabric Хранилище в Microsoft Fabric
Реляционные операторы PIVOT и UNPIVOT можно использовать для изменения возвращающего табличное значение выражения в другой таблице. PIVOT поворачивает возвращающее табличное значение выражение, преобразуя уникальные значения одного столбца выражения в несколько выходных столбцов. PIVOT также выполняет агрегаты, в которых они требуются для всех оставшихся значений столбцов, которые нужны в окончательных выходных данных. UNPIVOT выполняет обратную операцию PIVOT, вращая столбцы табличного выражения в значения столбцов.
Синтаксис для PIVOT более простого и более читаемого, чем синтаксис, который может быть указан в сложной серии инструкций SELECT...CASE . Полное описание синтаксиса смPIVOT. в предложении FROM.
Примечание.
Многократное использование PIVOT/UNPIVOT в одной инструкции T-SQL может негативно повлиять на производительность запросов.
Примеры кода Transact-SQL в этой статье используют AdventureWorks2022 базу данных или AdventureWorksDW2022 пример базы данных, которую можно скачать с домашней страницы примеров и проектов сообщества Microsoft SQL Server.
Синтаксис
В этом разделе описывается использование PIVOT оператора и UNPIVOT оператора.
Синтаксис оператора PIVOT .
SELECT [ <non-pivoted column> [ AS <column name> ] , ]
...
[ <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 become column headers>
IN ( <first pivoted column>
, <second pivoted column>
, ... <last pivoted column> )
) AS <alias for the pivot table>
[ <optional ORDER BY clause> ]
[ ; ]
Синтаксис оператора UNPIVOT .
SELECT [ <non-pivoted column> [ AS <column name> ] , ]
...
[ <output column for names of the pivot columns> [ AS <column name> ] , ]
[ <new output column created for values in result of the source query> [ AS <column name> ] ]
FROM
( <SELECT query that produces the data> )
AS <alias for the source query>
UNPIVOT
(
<new output column created for values in result of the source query>
FOR <output column for names of the pivot columns>
IN ( <first pivoted column>
, <second pivoted column>
, ... <last pivoted column> )
)
[ <optional ORDER BY clause> ]
[ ; ]
Замечания
Идентификаторы столбцов в предложении UNPIVOT следуют параметрам сортировки каталога.
Для База данных SQL Azure параметры сортировки всегда
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 выходных данных 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
Никакие продукты не определены со значением 3 для DaysToManufacture.
Следующий код отображает тот же самый результат, сведенный так, что значения DaysToManufacture становятся заголовками. Столбец предоставляется в течение трех ([3]) дней, даже если результаты имеются NULL.
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS CostSortedByProductionDays,
[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;
Вот результирующий набор.
CostSortedByProductionDays 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 числа, указанного в предложении сводной таблицы, которые являются сотрудниками250, , 251256и 257260 в этом примере. PurchaseOrderID служит столбцом значений, по которому группируются столбцы, возвращаемые в конечный вывод и называемые столбцами группирования. В этом случае значения столбцов группирования обрабатываются с помощью функции COUNT. Появится предупреждение, указывающее, что все значения NULL, отображаемые в столбце PurchaseOrderID , не рассматривались при вычислении COUNT каждого сотрудника.
Внимание
При использовании PIVOTагрегатных функций при вычислении агрегирования не учитывается наличие значений NULL в столбце значений.
Пример UNPIVOT
Оператор UNPIVOT выполняет действия, обратные оператору PIVOT, преобразуя столбцы данных в строки. Допустим, что таблица, созданная в ходе выполнения предыдущего примера, хранится в базе данных и имеет идентификатор pvt. Пусть необходимо преобразовать идентификаторы столбцов Emp1, Emp2, Emp3, Emp4 и Emp5 в строки данных, сгруппированные по поставщикам. Таким образом, необходимо определить два дополнительных столбца.
Столбец, содержащий значения столбцов, которые вы вращаете (Emp1Emp2и т. д.), вызывается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 не воспроизводит результат исходного табличного выражения, так как строки были объединены. Кроме того, NULL значения в входных UNPIVOT данных исчезают в выходных данных. Когда значения исчезают, оно показывает, что перед операцией могут быть исходные NULL значения PIVOT .
В представлении Sales.vSalesPersonSalesByFiscalYears образца базы данных AdventureWorks2022 предложение PIVOT используется для определения полного объема продаж каждого менеджера в течение каждого финансового года. Чтобы выполнить скрипт представления в СРЕДЕ SQL Server Management Studio, в обозреватель объектов найдите представление в папке AdventureWorks2022 "Представления" для базы данных. Щелкните правой кнопкой мыши имя представления и выберите Создать скрипт для представления.