FROM – Utilisation de PIVOT et UNPIVOT

• 14/10/2019
• 5 minutes de lecture
• • V
  • c
  • M
  • i
  • M
  • +7

Sapplique à: SQL Server (toutes les versions prises en charge) Azure SQL Database Instance gérée Azure SQL Azure Synapse Analytics Parallel Data Warehouse

Vous pouvez utiliser les opérateurs relationnels PIVOT et UNPIVOT pour changer une expression table en une autre table. PIVOT fait pivoter une expression table en transformant les valeurs uniques dune colonne de lexpression en plusieurs colonnes dans la sortie. Et PIVOT exécute les agrégations là où elles « sont requises sur toutes les valeurs de colonne restantes qui sont voulues dans la sortie finale. UNPIVOT effectue lopération inverse à PIVOT en faisant pivoter les colonnes dune expression table en valeurs de colonnes.

La syntaxe de PIVOT est plus simple et plus lisible que la syntaxe qui pourrait autrement lêtre spécifié dans une série complexe dinstructions SELECT...CASE. Pour une description complète de la syntaxe de PIVOT, voir FROM (Transact-SQL).

Syntaxe

La syntaxe suivante résume comment utiliser lopérateur PIVOT.

Remarques

Les identificateurs de colonne de la clause UNPIVOT suivent le classement du catalogue. Pour SQL Database, le classement est toujours SQL_Latin1_General_CP1_CI_AS. Pour SQL Server partiellement contenu bases de données, le classement est toujours Latin1_General_100_CI_AS_KS_WS_SC. Si la colonne est combin avec dautres colonnes, une clause dassemblage (COLLATE DATABASE_DEFAULT) est nécessaire pour éviter les conflits.

Exemple de base PIVOT

Lexemple de code suivant produit un tableau à deux colonnes qui comporte quatre lignes.

Voici lensemble de résultats.

Aucun produit nest défini avec trois DaysToManufacture.

Le code suivant affiche le même résultat, pivoté de sorte que DaysToManufacture deviennent les en-têtes de colonne. Une colonne est fournie pour trois jours, même si les résultats sont NULL.

Voici lensemble de résultats .

Exemple de PIVOT complexe

Un scénario courant où PIVOT peut être utile est lorsque vous souhaitez générer des rapports de tabulation croisée pour donner un résumé des données. Par exemple, supposons que vous souhaitiez interroger la table PurchaseOrderHeader dans la base de données exemple AdventureWorks2014 pour déterminer le nombre de bons de commande passés par certains employés. La requête suivante fournit ce rapport, trié par fournisseur.

Voici un ensemble de résultats partiel.

Les résultats renvoyés par cette sous-sélection sont pivotées sur la colonne EmployeeID.

Les valeurs uniques renvoyées par devient des champs dans lensemble de résultats final. En tant que tel, il existe « une colonne pour chaque EmployeeID nombre spécifié dans la clause pivot: dans ce cas, les employés 250, 251, 256, 257 et 260. Le PurchaseOrderID sert de colonne de valeur, par rapport à laquelle les colonnes renvoyées dans la sortie finale, appelées colonnes de regroupement, sont regroupées. Dans ce cas, les colonnes de regroupement sont agrégées par lélément COUNT. Notez quun message davertissement apparaît, indiquant que toute valeur NULL apparaissant dans la colonne PurchaseOrderID na pas été prise en compte lors du calcul de la COUNT pour chaque employé.

Exemple UNPIVOT

UNPIVOT effectue presque lopération inverse de PIVOT, en tournant les colonnes en lignes. Supposons que la table produite dans lexemple précédent soit stockée dans la base de données sous le nom pvt et que vous souhaitiez faire pivoter les identificateurs de colonne Emp1, Emp2, Emp3, Emp4 et Emp5 en valeurs de ligne qui correspondent à un fournisseur particulier. En tant que tel, vous devez identifier deux colonnes supplémentaires.La colonne qui contiendra les valeurs de colonne que vous « re-tournez (Emp1, Emp2, …) sera appelée Employee, et la colonne qui contiendra les valeurs qui existent actuellement sous les colonnes en cours de rotation sera appelée Orders. Ces colonnes correspondent aux colonnes pivot_column et value_column , respectivement, dans la définition Transact-SQL. Voici la requête.

Voici un ensemble de résultats partiel.

Notez que UNPIVOT nest pas exactement linverse de PIVOT. PIVOT effectue une agrégation et fusionne plusieurs lignes possibles en une seule ligne dans la sortie. UNPIVOT ne reproduit pas le résultat dorigine de lexpression table car les lignes ont été fusionnées. De plus, les valeurs nulles dans lentrée de UNPIVOT disparaissent dans la sortie. Lorsque les valeurs disparaissent, cela montre quil peut y avoir eu des valeurs nulles dorigine dans lentrée avant lopération PIVOT.

Le Sales.vSalesPersonSalesByFiscalYears dans la base de données exemple AdventureWorks2012 utilise PIVOT pour renvoyer le total des ventes de chaque vendeur, pour chaque exercice. Pour créer un script de la vue dans SQL Server Management Studio , dans lExplorateur dobjets, recherchez la vue sous le dossier Views de la base de données AdventureWorks2012. Cliquez avec le bouton droit sur le nom de la vue, puis sélectionnez Script View as.

Voir aussi

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