FROM - Utilisation des opérateurs PIVOT et UNPIVOTFROM - Using PIVOT and UNPIVOT

S’APPLIQUE À : ouiSQL Server (à partir de 2008) ouiAzure SQL Database ouiAzure SQL Data Warehouse ouiParallel Data Warehouse APPLIES TO: yesSQL Server (starting with 2008) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Vous pouvez utiliser les opérateurs de relation PIVOT et UNPIVOT pour modifier une expression table dans une autre table.You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. À partir d’une expression table, l’opérateur PIVOT transforme les valeurs uniques d’une colonne de l’expression en plusieurs colonnes de sortie et exécute les agrégations nécessaires sur les valeurs de colonne restantes qui doivent figurer dans la sortie finale.PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and runs aggregations where they're required on any left over column values that are wanted in the final output. L’opérateur UNPIVOT effectue l’opération inverse : il transforme les colonnes d’une expression table en valeurs de colonne.UNPIVOT carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

L’opérateur PIVOT a une syntaxe plus simple et plus lisible qu’une série complexe d’instructions SELECT...CASE.The syntax for PIVOT provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of SELECT...CASE statements. Pour obtenir la description complète de la syntaxe de PIVOT, consultez FROM (Transact-SQL).For a complete description of the syntax for PIVOT, see FROM (Transact-SQL).

SyntaxeSyntax

La syntaxe suivante récapitule comment utiliser l’opérateur PIVOT.The following syntax summarizes how to use the PIVOT operator.

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

Notes Remarks

Les identificateurs de colonne dans la clause UNPIVOT suivent le classement de catalogue.The column identifiers in the UNPIVOT clause follow the catalog collation. Pour SQL DatabaseSQL Database, le classement est toujours SQL_Latin1_General_CP1_CI_AS.For SQL DatabaseSQL Database, the collation is always SQL_Latin1_General_CP1_CI_AS. Pour les bases de données partiellement autonomes SQL ServerSQL Server, le classement est toujours Latin1_General_100_CI_AS_KS_WS_SC.For SQL ServerSQL Server partially contained databases, the collation is always 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.If the column is combined with other columns, then a collate clause (COLLATE DATABASE_DEFAULT) is required to avoid conflicts.

Exemple PIVOT de baseBasic PIVOT Example

L'exemple de code suivant produit un tableau à deux colonnes et quatre lignes.The following code example produces a two-column table that has four rows.

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

Voici l'ensemble des résultats.Here is the result set.

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

Aucun produit n'est défini avec trois DaysToManufacture.No products are defined with three DaysToManufacture.

Le code suivant affiche le même résultat, croisé dynamiquement pour que les valeurs DaysToManufacture deviennent les en-têtes de colonne.The following code displays the same result, pivoted so that the DaysToManufacture values become the column headings. Une colonne est fournie pour trois [3] jours même si les résultats sont NULL.A column is provided for three [3] days, even though the results are 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;  
  

Voici l'ensemble des résultats.Here is the result set.

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

Exemple PIVOT complexeComplex PIVOT Example

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.A common scenario where PIVOT can be useful is when you want to generate cross-tabulation reports to give a summary of the data. Par exemple, supposons que vous souhaitiez interroger la table PurchaseOrderHeader de l'exemple de base de données AdventureWorks2014 pour déterminer le nombre de commandes traitées par certains employés.For example, suppose you want to query the PurchaseOrderHeader table in the AdventureWorks2014 sample database to determine the number of purchase orders placed by certain employees. La requête suivante fournit ce rapport, ventilé par fournisseur.The following query provides this report, ordered by vendor.

USE AdventureWorks2014;  
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.Here is a partial result set.

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.The results returned by this subselect statement are pivoted on the EmployeeID column.

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.The unique values returned by the EmployeeID column become fields in the final result set. Par conséquent, il existe une colonne pour chaque numéro EmployeeID spécifié dans la clause PIVOT : dans ce cas les employés 164, 198, 223, 231 et 233.As such, there's a column for each EmployeeID number specified in the pivot clause: in this case employees 164, 198, 223, 231, and 233. 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.The PurchaseOrderID column serves as the value column, against which the columns returned in the final output, which are called the grouping columns, are grouped. Dans ce cas, les colonnes de regroupement sont agrégées par la fonction COUNT.In this case, the grouping columns are aggregated by the COUNT function. Un message d'avertissement apparaît indiquant qu'aucune valeur NULL figurant dans la colonne PurchaseOrderID n'a été prise en compte pour le calcul de la valeur COUNT de chaque employé.Notice that a warning message appears that indicates that any null values appearing in the PurchaseOrderID column weren't considered when computing the COUNT for each employee.

Important

Quand vous utilisez des fonctions d’agrégation avec l’opérateur PIVOT, les valeurs NULL présentes dans la colonne de valeurs ne sont pas prises en compte lors du calcul d’une agrégation.When aggregate functions are used with PIVOT, the presence of any null values in the value column are not considered when computing an aggregation.

L’opérateur UNPIVOT effectue pratiquement l’opération inverse de l’opérateur PIVOT, en transformant des colonnes en lignes.UNPIVOT carries out almost the reverse operation of PIVOT, by rotating columns into rows. 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.Suppose the table produced in the previous example is stored in the database as pvt, and you want to rotate the column identifiers Emp1, Emp2, Emp3, Emp4, and Emp5 into row values that correspond to a particular vendor. Cela signifie que vous devez identifier deux colonnes supplémentaires.As such, you must identify two additional columns. La colonne qui doit contenir les valeurs de colonne transformées (Emp1, Emp2,...) est la colonne Employee tandis que la colonne destinée à contenir les valeurs existant actuellement dans les colonnes subissant la transformation est la colonne Orders.The column that will contain the column values that you're rotating (Emp1, Emp2,...) will be called Employee, and the column that will hold the values that currently exist under the columns being rotated will be called Orders. Ces colonnes correspondent respectivement aux paramètres pivot_column et value_column dans la définition Transact-SQLTransact-SQL.These columns correspond to the pivot_column and value_column, respectively, in the Transact-SQLTransact-SQL definition. La requête est la suivante.Here is the query.

-- 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.Here is a partial result set.

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

L’opérateur UNPIVOT n’est pas l’exact opposé de l’opérateur PIVOT.Notice that UNPIVOT isn't the exact reverse of PIVOT. L’opérateur PIVOT effectue une agrégation et fusionne plusieurs lignes possibles en une ligne unique dans la sortie.PIVOT carries out an aggregation and merges possible multiple rows into a single row in the output. L’opérateur UNPIVOT ne regénère pas le résultat de l’expression table d’origine après la fusion des lignes.UNPIVOT doesn't reproduce the original table-valued expression result because rows have been merged. En outre, les valeurs null dans l’entrée de UNPIVOT disparaissent dans la sortie.Also, null values in the input of UNPIVOT disappear in the output. Lorsque les valeurs disparaissent, cela indique qu’il y avait peut-être des valeurs null d’origine dans l’entrée avant l’opération PIVOT.When the values disappear, it shows that there may have been original null values in the input before the PIVOT operation.

L’affichage Sales.vSalesPersonSalesByFiscalYears de l’exemple de base de données AdventureWorks2012AdventureWorks2012 utilise l’opérateur PIVOT pour retourner le total des ventes de chaque vendeur, par exercice comptable.The Sales.vSalesPersonSalesByFiscalYears view in the AdventureWorks2012AdventureWorks2012 sample database uses PIVOT to return the total sales for each salesperson, for each fiscal year. Pour générer le script de l’affichage dans SQL Server Management StudioSQL Server Management Studio, dans l’Explorateur d’objets, recherchez l’affichage dans le dossier Vues de la base de données AdventureWorks2012AdventureWorks2012.To script the view in SQL Server Management StudioSQL Server Management Studio, in Object Explorer, locate the view under the Views folder for the AdventureWorks2012AdventureWorks2012 database. 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.Right-click the view name, and then select Script View as.

 Voir aussiSee Also

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