Cet article donnera un aperçu complet de l'utilisation des opérateurs PIVOT et UNPIVOT dans SQL Server. Les opérateurs PIVOT et UNPIVOT sont similaires aux opérateurs relationnels qui permettent transformer l'expression table en une autre table . Les deux opérateurs génèrent des rapports multidimensionnels qui permettent de combiner et de comparer rapidement une grande quantité de données.
Nous pouvons utiliser le Opérateur PIVOT lorsque nous devons transformer des expressions table. Il divise le valeurs uniques d'une colonne dans plusieurs colonnes dans le résultat final. Ça aussi agrégats les valeurs de colonne restantes requises dans le résultat final. Opérateur UNPIVOT convertit les données des colonnes d'une expression table en valeurs de colonne, ce qui est l'inverse de PIVOT.
Comprenons-le à l’aide du schéma simple ci-dessous :
Sur le côté gauche de cette figure, on peut voir le ensemble de données d'origine , qui comporte trois colonnes : Année, région, et Ventes . Ensuite, nous pouvons voir le tableau PIVOT sur le côté droit, qui est construit en faisant pivoter le Région (lignes) en Nord et Sud (colonnes) . Après avoir converti les lignes en colonnes, nous pouvons faire une agrégat des valeurs de la colonne Ventes pour chaque intersection entre les colonnes et les lignes du tableau PIVOT.
Créons d'abord une table nommée pivot_démo pour démontrer les opérateurs PIVOT et UNPIVOT. L'instruction suivante crée une nouvelle table dans notre base de données spécifiée :
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
Ensuite, insérez quelques données dans ce tableau comme ci-dessous :
INSERT INTO pivot_demo VALUES ('North', 2010, 72500), ('South', 2010, 60500), ('South', 2010, 52000), ('North', 2011, 45000), ('South', 2011, 82500), ('North', 2011, 35600), ('South', 2012, 32500), ('North', 2010, 20500);
Nous pouvons vérifier les données à l'aide de l'instruction SELECT. Nous obtiendrons le résultat ci-dessous :
Opérateur PIVOT
Cet opérateur est utilisé pour faire pivoter les expressions table. Il a été introduit pour la première fois dans la version SQL Server 2005. Il convertit les données des lignes en colonnes. Il divise les valeurs uniques d'une colonne en plusieurs colonnes, puis agrège les valeurs de colonne restantes requises dans le résultat final.
Nous devons suivre les étapes suivantes pour créer un tableau PIVOT :
- Sélectionnez l’ensemble de données de base pour le pivotement.
- Créez des résultats temporaires à l'aide d'une table dérivée ou CTE (expression de table commune).
- Utilisez l'opérateur PIVOT.
Syntaxe
La syntaxe suivante illustre l'utilisation de PIVOT dans SQL Server :
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Si nous cassons ce script, nous pouvons voir qu’il comporte deux sections distinctes. La première section sélectionne les données de la table principale et la deuxième section détermine comment la table PIVOT sera construite. La deuxième partie contient également des mots-clés spéciaux tels que SUM, FOR et IN. Voyons la signification de ces mots-clés dans l'opérateur PIVOT.
survoler en CSS
SOMME
Cet opérateur est utilisé pour agréger les valeurs de la colonne spécifiée à utiliser dans la table PIVOT. Nous devons l'utiliser avec l'opérateur PIVOT pour obtenir les affichages de colonnes agrégées pour les sections de valeurs.
POUR Mot-clé
Ce mot-clé est utilisé pour l'instruction de table PIVOT pour instruire l'opérateur PIVOT sur quelle colonne la fonction PIVOT doit être appliquée. Fondamentalement, il indique les noms de colonnes qui passeront de lignes en colonnes.
DANS Mot-clé
Ce mot-clé répertorie toutes les valeurs uniques de la colonne PIVOT à afficher comme colonnes de la table PIVOT.
changer de Java
Exemple
Comprenons-le à l'aide de divers exemples.
1. L'instruction suivante sélectionne d'abord les colonnes Année, Nord et Sud comme données de base pour le pivotement. Ensuite, créez un résultat temporaire à l'aide de la table dérivée et appliquez enfin l'opérateur PIVOT pour générer la sortie finale. Cette production est également ordonnée dans l'année ascendante.
SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS Tab2 ORDER BY Tab2.Year
L'exécution de cette instruction produira la sortie ci-dessous. Ici, nous pouvons voir le somme calculée des ventes des régions Nord et Sud correspondant aux valeurs de l'année .
2. Ceci est un autre exemple où nous allons calculer la somme des ventes pour chaque année correspondant aux valeurs de la région :
SELECT Region, 2010, 2011, 2012 FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN (2010, 2011, 2012)) AS Tab2 ORDER BY Tab2.Region;
L'exécution de cette instruction produire une erreur car nous ne pouvons pas spécifier directement la valeur numérique comme nom de colonne.
Cependant, SQL Server nous permet d'éviter ce problème en utilisant les parenthèses avant chaque valeur entière. L'instruction mise à jour est affichée dans l'extrait de code suivant :
SELECT Region, [2010], [2011], [2012] FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN ([2010], [2011], [2012])) AS Tab2 ORDER BY Tab2.Region;
Cette instruction s'exécute avec succès et affiche la somme calculée des ventes pour chaque année correspondant aux valeurs de la région :
3. L'exemple précédent d'obtention d'une table PIVOT est utile lorsque nous connaissons toutes les valeurs possibles de la colonne PIVOT. Mais supposons que l’année prochaine, le nombre de colonnes augmente. En considérant l'exemple précédent, nous avons les années 2010, 2011 et 2012 comme colonnes PIVOT. Cependant, rien ne garantit que ces colonnes ne changeront pas à l'avenir. Que se passe-t-il si nous avons des données de 2013 ou 2014, ou peut-être même plus ? Dans de tels cas, nous devrons utiliser tableau PIVOT dynamique requêtes pour résoudre ce problème.
La requête de table PIVOT dynamique encapsule l'intégralité du script PIVOT dans une procédure stockée. Cette procédure fournira des options réglables, nous permettant de modifier nos exigences en changeant quelques valeurs paramétrées.
constantes Java
Le code SQL suivant explique le fonctionnement de la table PIVOT dynamique. Dans ce script, nous avons d'abord récupéré toutes les valeurs distinctes de la colonne PIVOT, puis écrit une instruction SQL à exécuter avec la requête PIVOT au moment de l'exécution. Voyons le résultat après l'exécution de ce script :
CREATE PROCEDURE DynamicPivotTable @PivotColumn NVARCHAR(255), @PivotList NVARCHAR(255) AS BEGIN DECLARE @Query NVARCHAR(MAX); SET @Query = N' SELECT * FROM (SELECT [Region], [Year], [Sales] FROM pivot_demo) AS tab1 PIVOT (SUM([Sales]) FOR ['+@Pivot_Column+'] IN ('+@Pivot_List+')) AS PivotTable'; EXEC(@Query) END
Dans ce script, nous avons créé deux variables paramétrées. Sa description est donnée ci-dessous :
@PivotColumn : Cette variable prendra le nom de la colonne de la table d'origine sur laquelle la table PIVOT est créée. Par exemple , ici, la colonne 'Région' affiche toutes les régions disponibles dans les colonnes.
@ListePivot : Cette variable prendra la liste de colonnes que nous souhaitons afficher comme colonne de sortie dans la table PIVOT.
Exécution d'une procédure stockée dynamique
Après la création réussie de la procédure stockée dynamique, nous sommes prêts à l'exécuter. L'instruction suivante est utilisée pour appeler la procédure stockée dynamique afin d'afficher la table PIVOT au moment de l'exécution :
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Ici, nous avons maintenant spécifié le nom de la colonne ' Région ' comme premier paramètre et la liste de colonnes PIVOT comme deuxième paramètre. L'exécution du script affichera le résultat suivant :
Désormais, nous pouvons ajouter plus de colonnes à l'avenir lors de l'exécution pour afficher la table PIVOT, ce qui n'est pas possible avec les deux premiers exemples.
Opérateur UNPIVOT
Il s'agit de la méthode inverse de l'opérateur PIVOT dans SQL Server. C'est cet opérateur qui effectue le fonctionnement inverse du PIVOT en convertissant les données des colonnes en lignes. L'opérateur UNPIVOT fait également pivoter la table PIVOT vers la table normale. Il a été introduit pour la première fois dans la version SQL Server 2005.
Syntaxe
La syntaxe suivante illustre UNPIVOT dans SQL Server :
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
Exemple
Comprenons comment UNPIVOT l'opération PIVOT avec des exemples. Nous allons d'abord créer un table originale et table PIVOT puis appliqué l'opérateur UNPIVOT sur cette table.
L'extrait de code suivant déclare d'abord une variable de table temporaire @Tab :
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
Ensuite, nous insérerons des valeurs dans ce tableau comme ci-dessous :
INSERT INTO @Tab SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ORDER BY PivotTable.Year
Maintenant, nous pouvons effectuer l'opération UNPIVOT en utilisant l'instruction ci-dessous :
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
L'exécution de l'extrait de code renverra le résultat suivant :
nettoyer le cache npm
L'extrait de code ci-dessous est un autre exemple pour effectuer d'abord une opération PIVOT, puis une opération UNPIVOT sur la même table au sein d'une seule requête :
SELECT Region, Year, Sales FROM ( SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ) P --Perform UNPIVOT Operation UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
L'exécution de l'extrait de code affichera le même résultat :
REMARQUE : Le processus UNPIVOT est une opération inverse de la procédure PIVOT, mais il ne s'agit pas d'une inversion exacte. Étant donné que les lignes ont été fusionnées lorsque PIVOT calcule l'agrégat et combine plusieurs lignes en une seule ligne dans le résultat, l'opération UNPIVOT ne peut pas rendre le tableau semblable à l'original. Cependant, si l'opérateur PIVOT ne fusionne pas plusieurs lignes en une seule, l'opérateur UNPIVOT peut obtenir la table d'origine à partir de la sortie PIVOT.
Conclusion
Cet article donnera un aperçu complet des opérateurs PIVOT et UNPIVOT dans SQL Server et convertira une expression de table en une autre. Il ne faut jamais oublier que UNPIVOT est l'opération inverse de PIVOT, mais ce n'est pas l'exact inverse du résultat PIVOT.