FROM - Utilisation des opérateurs PIVOT et UNPIVOT

  • Article

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Vous pouvez utiliser les opérateurs de relation PIVOT et UNPIVOT pour modifier une expression table dans une autre table. PIVOT fait pivoter une expression de table en activant les valeurs uniques d’une colonne de l’expression en plusieurs colonnes dans la sortie. PIVOT exécute également des agrégations où elles sont requises sur les valeurs de colonne restantes souhaitées dans la sortie finale. UNPIVOT exécute l’opération opposée à PIVOT, en faisant pivoter des colonnes d’une expression table en valeurs de colonne.

La syntaxe est PIVOT plus facile et plus lisible que la syntaxe qui peut autrement être spécifiée dans une série complexe d’instructions SELECT...CASE . Pour obtenir une description complète de la syntaxe pour PIVOT, consultez la clause FROM.

Remarque

L’utilisation répétée d’une instruction T-SQL unique peut avoir un impact négatif sur les performances des PIVOT/UNPIVOT requêtes.

Les exemples de code Transact-SQL de cet article sont fondés sur l’échantillon de base de données AdventureWorks2022 ou AdventureWorksDW2022 fourni, que vous pouvez télécharger à partir de la page d’accueil Échantillons et projets communautaires Microsoft SQL Server.

Syntaxe

Cette section récapitule comment utiliser l’opérateur et UNPIVOT l’opérateurPIVOT.

Syntaxe de l’opérateur 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> ]
[ ; ]

Syntaxe de l’opérateur 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> ]
[ ; ]

Notes

Les identificateurs de colonne dans la clause UNPIVOT suivent le classement de catalogue.

  • Pour Azure SQL Database, le classement est toujours SQL_Latin1_General_CP1_CI_AS.

  • Pour les bases de données partiellement autonomes SQL Server, le classement est toujours Latin1_General_100_CI_AS_KS_WS_SC.

Si la colonne est combinée avec d’autres colonnes, une clause Collate (COLLATE DATABASE_DEFAULT) doit être ajoutée pour éviter les conflits.

Dans les pools Microsoft Fabric et Azure Synapse Analytics, les requêtes avec PIVOT opérateur échouent en GROUP BY cas de sortie PIVOTde colonne nonpivot. Pour contourner ce problème, supprimez la colonne nonpivot de l’objet GROUP BY. Les résultats de la requête sont identiques, car cette GROUP BY clause est un doublon.

Exemple PIVOT basique

L'exemple de code suivant produit un tableau à deux colonnes et quatre lignes.

USE AdventureWorks2022;
GO

SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;

Voici le jeu de résultats.

DaysToManufacture  AverageCost
------------------ ------------
0                  5.0885
1                  223.88
2                  359.1082
4                  949.4105

Aucun produit n’est défini avec la valeur de 3 DaysToManufacture.

Le code suivant affiche le même résultat, croisé dynamiquement pour que les valeurs DaysToManufacture deviennent les en-têtes de colonne. Une colonne est fournie pendant trois ([3]) jours, même si les résultats sont 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;

Voici le jeu de résultats.

CostSortedByProductionDays  0           1           2           3           4
--------------------------- ----------- ----------- ----------- ----------- -----------
AverageCost                 5.0885      223.88      359.1082    NULL        949.4105

Exemple PIVOT complexe

Un scénario classique consiste à utiliser l'opérateur PIVOT pour générer des rapports à tabulation croisée afin de synthétiser des données. Par exemple, supposons que vous souhaitiez interroger la table PurchaseOrderHeader de l'exemple de base de données AdventureWorks2022 pour déterminer le nombre de commandes traitées par certains employés. La requête suivante fournit ce rapport, ventilé par fournisseur.

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;

Voici un jeu de résultats partiel.

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

Les résultats retournés par cette instruction de sous-sélection sont croisés dynamiquement sur la colonne EmployeeID.

SELECT PurchaseOrderID,
    EmployeeID,
    VendorID
FROM PurchaseOrderHeader;

Les valeurs uniques retournées par la colonne EmployeeID deviennent des champs dans l'ensemble de résultats final. Par conséquent, il existe une colonne pour chaque EmployeeID nombre spécifié dans la clause pivot, qui sont employés 250, 251, 256, 257, et 260 dans cet exemple. La colonne PurchaseOrderID sert de colonne de valeur par rapport à laquelle les colonnes retournées dans la sortie finale (colonnes de regroupement) sont regroupées. Dans ce cas, les colonnes de regroupement sont agrégées par la fonction COUNT. Un message d’avertissement s’affiche qui indique que toutes les valeurs Null qui apparaissent dans la colonne n’ont pas été prises en compte lors de l’informatique PurchaseOrderID COUNT de chaque employé.

Important

Lorsque les fonctions d’agrégation sont utilisées avec PIVOT, la présence de valeurs Null dans la colonne valeur n’est pas prise en compte lors du calcul d’une agrégation.

Exemple UNPIVOT

L’opérateur UNPIVOT effectue pratiquement l’opération inverse de l’opérateur PIVOT, en transformant des colonnes en lignes. Supposons que la table générée dans l'exemple précédent soit stockée dans la base de données sous le nom pvt et que vous souhaitiez transformer les identificateurs de colonne Emp1, Emp2, Emp3, Emp4 et Emp5 en valeurs de ligne correspondant à un fournisseur particulier. Par conséquent, vous devez identifier deux colonnes supplémentaires.

La colonne qui contient les valeurs de colonne que vous effectuez une rotation (Emp1, Emp2et ainsi de suite) est appelée Employee, et la colonne qui contient les valeurs qui existent actuellement sous les colonnes en cours de rotation est appelée Orders. Ces colonnes correspondent respectivement aux paramètres pivot_column et value_column dans la définition Transact-SQL. Voici la requête.

-- 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

Voici un jeu de résultats partiel.

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 n’est pas exactement l’inverse de PIVOT. L’opérateur PIVOT effectue une agrégation et fusionne plusieurs lignes possibles en une ligne unique dans la sortie. UNPIVOT ne reproduit pas le résultat de l’expression table d’origine, car les lignes ont été fusionnées. En outre, NULL les valeurs dans l’entrée de UNPIVOT disparition dans la sortie. Lorsque les valeurs disparaissent, il indique qu’il peut y avoir eu des valeurs d’origine NULL dans l’entrée avant l’opération PIVOT .

L’affichage Sales.vSalesPersonSalesByFiscalYears de l’exemple de base de données AdventureWorks2022 utilise l’opérateur PIVOT pour retourner le total des ventes de chaque vendeur, par exercice comptable. Pour générer le script de l’affichage dans SQL Server Management Studio, dans Explorateur d’objets, recherchez l’affichage dans le dossier Affichages de la base de données AdventureWorks2022. Cliquez avec le bouton droit sur le nom de l’affichage, puis sélectionnez Générer un script de la vue en tant que.

Contenu connexe