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

Статья
10/17/2024

Область применения: 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 "Представления" для базы данных. Щелкните правой кнопкой мыши имя представления и выберите Создать скрипт для представления.

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

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

Синтаксис

Замечания

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

Сложный пример PIVOT

Пример UNPIVOT

Обратная связь

Дополнительные ресурсы

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

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

Синтаксис

Замечания

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

Сложный пример PIVOT

Пример UNPIVOT

Связанный контент

Обратная связь

Дополнительные ресурсы