Dans cet article, nous allons parcourir les opérateurs Pivot SQL et Unpivot SQL et comment ils peuvent être utiles pour transposer des données SQL Server. En outre, nous discuterons des moyens statiques et dynamiques d’utiliser des opérateurs relationnels PIVOT et UNPIVOT qui peuvent être utilisés pour transformer des valeurs distinctes agrégées en colonne (s) dans le jeu de résultats en spécifiant toutes les valeurs de colonne dans la clause PIVOT IN.
Introduction au pivot SQL Server
Il existe plusieurs façons de transposer un ensemble de données de lignes en colonnes et de colonnes en lignes. Le pivot SQL est l’une des techniques qui permet de transposer des lignes en colonnes et d’effectuer des agrégations possibles en cours de route. Les opérateurs relationnels SQL PIVOT et SQL UNPIVOT transposent des données bidimensionnelles à valeur de table dans une autre forme de données. SQL PIVOT transpose une expression à valeur de table d’un ensemble unique de valeurs d’une colonne en plusieurs colonnes dans la sortie et effectue des agrégations. SQL UNPIVOT effectue l’opération inverse de SQL PIVOT en transformant les colonnes d’une expression à valeur de table en valeurs de colonne.
Dans la plupart des cas, la technique de pivotement statique suffit aux besoins de l’entreprise. Par exemple, la prévision des ventes mensuelles, la division des ventes annuelles, l’agrégation des ventes trimestrielles, etc., où les colonnes de la clause IN restent statiques. Dans d’autres cas, nous avons besoin de détails granulaires et l’expression de valeur de table est de nature plus dynamique et tout le temps un nouvel ensemble de l’expression est inclus dans la table, alors le PIVOT dynamique serait le meilleur choix.
Remarque: Dans la représentation ci-dessus, nous pouvons voir que dans le processus de pivotement, les valeurs de colonne sont pivotées de la verticale à l’horizontale et le DÉPIVOTAGE revient à les faire pivoter de l’horizontale à la verticale.
Syntax
SELECT <non-pivoted column>,
AS <column alias>,
AS <column alias>,
…
AS <column alias>
FROM
(
<SELECT QUERY>)
AS <Alias for temporary data set>
PIVOT
(
<Aggregate function>( Aggregate column)
FOR
IN ( , ,
… )
) AS < Alias de tableau croisé dynamique >
<ORDRE PAR clause >;
Mise en route avec SQL Pivot
Nous allons commencer par pivoter et dés-pivoter les données. Vous comprenez maintenant quels sont les opérateurs PIVOT et UNPIVOT dans SQL Server. En termes simples, c’est juste une autre façon de décrire le processus de transformation de valeurs de ligne distinctes en leurs propres colonnes est connu sous le nom de pivotement. Et le processus de transformation des colonnes en lignes est connu sous le nom de non-pivotement.
Exemple 1: Comment effectuer une opération de pivot SQL
Imaginons l’exemple d’ensemble de données dérivé de la base de données AdventureWorks2014. Dans ce cas, vous venez d’obtenir un ensemble de données simple avec une colonne SalesYear le long de la valeur quantitative TotalSales.
Maintenant, l’ensemble de données est prêt à pivoter. Vous avez SalesYear dans la première colonne, TotalSales dans la deuxième colonne. Si vous avez demandé de transposer les données, vous devez prendre chaque année distincte dans la colonne et ces colonnes deviennent l’en-tête des colonnes pivot. Ainsi, 2011 devient la première colonne, 2012 devient sa propre colonne et ainsi de suite. Les valeurs réelles de la colonne suivante Totalsales, elles s’en tiennent aux en-têtes de colonne pivot. Maintenant, la table pivotante ressemblerait à ci-dessous.
|
1
2
3
4
5
6
7
8
9
10
|
SELECT* FROM
(
SÉLECTIONNEZ L’ANNÉE (SOH.OrderDate) en tant que SalesYear,
SOH.Sous-total en tant que TotalSales
DES ventes.SalesOrderHeader SOH
REJOIGNEZ les ventes.SalesOrderDetail SOD SUR SOH.SalesOrderId = SOD.SalesOrderId
) EN TANT QUE Sales
PIVOT(SUM(TotalSales)
POUR SalesYear IN(,,,))
en tant que PVT
|
L’image suivante représente les données d’entrée, la syntaxe du pivot, la Pivot SQL et résultats de sortie.
Exemple 2: Comment effectuer une opération SQL Unpivot
Regardons maintenant dans l’autre sens, sans pivoter, ce qui est aussi simple que de passer de l’horizontale à la verticale. Dans ce cas, vous prendriez toutes les colonnes distinctes sélectionnées, et cela transformerait ces en-têtes de colonne en leurs propres lignes. Le pivotement est comme une rotation de la verticale à l’horizontale. Et le non-pivotement est comme une rotation de l’horizontale à la verticale.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SÉLECTIONNEZ SalesYear,
TotalSales
À PARTIR DU
(
SÉLECTIONNEZ *
À PARTIR DE la
(
SÉLECTIONNEZ L’ANNÉE (SOH.OrderDate) EN TANT QUE SalesYear,
SOH.Sous-total EN TANT QUE TotalSales
DES ventes.SalesOrderHeader SOH
REJOIGNEZ les ventes.SalesOrderDetail SOD SUR SOH.SalesOrderId = SOD.En TANT QUE PIVOT DES ventes (SUM(TotalSales) POUR SalesYear IN(,
,
,
)) EN TANT QUE PVT
) T UNPIVOT (TotalSales POUR SalesYear IN(,
,
,
,
)) EN TANT QUE upvt;
|
Dans l’exemple suivant, l’ensemble de données pivoté est utilisé pour dé-pivoter les valeurs. La pièce clé ici est que c’était facile à faire pour se transformer car nous avons pu créer un tableau croisé dynamique comme étape intermédiaire, puis effectuer la transformation en utilisant l’opération unpivot.
Remarque: L’opération de pivot SQL permet de transposer le résultat agrégé en colonne mais alors que SQL unpivot n’est pas une inversion exacte de transposer des colonnes dans les valeurs séparées des lignes. L’opérateur unpivot ne divisera pas les résultats agrégés.
Exemple 3: Comment obtenir des ventes trimestrielles à l’aide de l’opération pivot SQL
Voyons quelques exemples supplémentaires pour mieux comprendre les concepts de pivotement. Dans ce cas, nous verrons comment obtenir des données de ventes trimestrielles agrégées en fonction des ventes trimestrielles.
Avant de nous lancer dans la solution, il est toujours recommandé de travailler sur la création d’un ensemble de données pour l’opération de PIVOT.
L’exemple suivant donne les ventes mensuelles agrégées réparties sur l’année de vente.
|
1
2
3
4
5
6
7
8
9
|
SÉLECTIONNEZ L’ANNÉE (SOH.OrderDate) COMME ANNÉE de vente,
MOIS (SOH.OrderDate) EN TANT QUE MOIS de vente,
SOMME (SOH.Sous-total) COMME TotalSales
DES ventes.SalesOrderHeader SOH
REJOIGNEZ les ventes.SalesOrderDetail SOD SUR SOH.SalesOrderId = SOD.SalesOrderId
GROUPE PAR ANNÉE (SOH.OrderDate),
MOIS (SOH.OrderDate)
ORDRE PAR ANNÉE (SOH.OrderDate),
MOIS (SOH.OrderDate);
|
La sortie suivante affiche les données de base
Une fois que vous êtes prêt avec les données de base, vous pouvez appliquer l’opérateur de PIVOT. Dans l’exemple suivant, la clause IN accepte un caractère non numérique généré en fonction de la valeur DATEPART. La valeur DATEPART est concaténée avec ‘Q’, puis les valeurs sont transmises à la clause IN de l’opérateur PIVOT.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SÉLECTIONNEZ SalesYear,
ISNULL(,0) COMME Q1,
ISNULL(,0) COMME Q2,
iv JE n’AI PAS DE problème AVEC LE FAIT QUE JE n’AI PAS DE problème AVEC LE FAIT QUE JE n’AI PAS DE problème AVEC LE FAIT QUE JE n’AI PAS DE problème AVEC LE FAIT QUE JE n’AI PAS DE problème AVEC LE FAIT QUE JE n’AI PAS DE problème AVEC LE fait QUE JE n’AI PAS DE problème AVEC LE fait QUE JE n’AI PAS DE problème AVEC LE fait QUE JE n’AI PAS DE problème AVEC LE fait QUE JE n’AI PAS DE problème AVEC LE fait QUE JE NE PEUX PAS LE faire., Si vous avez BESOIN D’UN NOM DE domaine, VOUS POUVEZ LE FAIRE À PARTIR D’UN NOM DE DOMAINE OU D’UN NOM DE DOMAINE.OrderDate) EN TANT QUE SalesYear, CAST(‘Q’ + CAST(DATEPART(QUARTER, SOH.OrderDate) COMME VARCHAR(1)) COMME VARCHAR(2)) Quarts,
SOH.Sous-total EN TANT QUE TotalSales
DES ventes.SalesOrderHeader SOH
REJOIGNEZ les ventes.SalesOrderDetail SOD SUR SOH.SalesOrderId = SOD.SalesOrderId
) EN TANT QUE PIVOT DE données (SOMME (TotalSales) POUR LES trimestres DANS (,
,
,
)) EN TANT QUE pvt
ORDRE PAR SalesYear;
|
La sortie suivante est une division trimestrielle des données de vente
Exemple 4: Comment obtenir des ventes mensuelles à l’aide d’une opération de pivot SQL
Jetons un coup d’œil à un autre exemple pour voir les données de division des ventes basées sur chaque mois. Dans l’exemple suivant, les valeurs de la clause IN sont générées dans le cadre de la fonction DATEPART. Les valeurs DATEPART sont alimentées DANS la clause de l’opérateur PIVOT.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
SÉLECTIONNEZ SalesYear,
Isnull(,0) en janvier,
Isnull(,0) en février,
Isnull(,0) en mars,
ISNULL(,0) en Avril,
isnull(,0) en mai,
isnull(,0) comme Juin,
isnull(,0) comme Juillet,
isnull(,0) comme Août,
isnull(,0) comme Septembre,
ISNULL(,0) comme Octobre,
isnull(,0) comme Novembre,
isnull(,0) comme Dec,
(isnull(,0) + isnull(,0) + ISNULL(,0) + isnull(,0) + isnull(,0) + isnull(,0) + ISNULL(,0) + isnull(,0) + isnull(,0)+ isnull(,0)+ isnull(,0)+ isnull(,0) + isnull(,0) + isnull(,0) + Si vous avez un problème, vous pouvez le faire À partir DE la page d’accueil de la page d’accueil de la page d’accueil de la page d’accueil de la page d’accueil de la page d’accueil de la page D’accueil DE la page d’accueil DE la page D’ACCUEIL DE la page D’ACCUEIL DE la page D’ACCUEIL DE la page D’ACCUEIL DE la page D’ACCUEIL DE la page D’ACCUEIL DE la page D’ACCUEIL DE la page D’ACCUEIL DE la page D’ACCUEIL DE la page D’ACCUEIL DE la page D’ACCUEIL DE la page D’ACCUEIL DE la page D’ACCUEIL DE la page D’ACCUEIL DE la page D’ACCUEIL.OrderDate) EN TANT QUE SalesYear,
DATEPART(MOIS, SOH.Date de commande) Mois,
SOH.Sous-total EN TANT QUE TotalSales
DES ventes.SalesOrderHeader SOH
REJOIGNEZ les ventes.SalesOrderDetail SOD SUR SOH.SalesOrderId = SOD.En TANT QUE PIVOT DE données (SOMME (TotalSales) POUR LES Mois DANS (,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
,
>,
)) EN TANT QUE pvt;
|
La sortie suivante est une division mensuelle des données de vente
Exemple 5: Comment obtenir des ventes mensuelles à l’aide d’une opération de pivot SQL dynamique
Abordons le problème fractionnement mensuel des données de vente à l’aide du pivotement dynamique. Jusqu’à présent, nous avons discuté des opérations de pivot statique. Afin de convertir une opération de pivot statique en dynamique, nous devons supprimer les valeurs codées en dur de la clause IN. Tout d’abord, récupérez l’expression distincte de l’ensemble de données, puis préparez une chaîne en concaténant toutes les expressions. Dans l’exemple suivant, le @columnname est utilisé pour concaténer toutes les expressions. L’expression concaténée est introduite dans la clause pivot IN. Le reste est une simple conversion de SQL statique en SQL dynamique et appelle @DML en utilisant la procédure stockée sp_executesql.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
INDIQUANT @dml COMME NVARCHAR(MAX)
DÉCLARÉ SI VOUS AVEZ UN nom DE colonne COMME NVARCHAR(MAX)
SÉLECTIONNEZ @ColumnName = ISNULL(@ColumnName +’,’, »)
+QUOTENAME(Mois)
DE (SÉLECTIONNEZ DATEPART DISTINCT(MOIS, SOH.Date de commande) Mois
DES ventes.SalesOrderHeader SOH
REJOIGNEZ les ventes.SalesOrderDetail SOD SUR SOH.SalesOrderId = SOD.SalesOrderId
GROUPE PAR ANNÉE (SOH.OrderDate),
DATEPART(MOIS, SOH.OrderDate)) SOUS forme de mois
Prepare Prépare la requête PIVOT en utilisant la dynamique
SET @dml=
N’SÉLECTIONNEZ SalesYear, ‘+@ColumnName+’ DE
(
SELECT YEAR(SOH.OrderDate) EN TANT QUE SalesYear,
DATEPART(MOIS, SOH.OrderDate) Mois,
SOMME (SOH.Sous-total) COMME TotalSales
DES ventes.SalesOrderHeader SOH
REJOIGNEZ les ventes.SalesOrderDetail SOD SUR SOH.SalesOrderId = SOD.SalesOrderId
GROUPE PAR ANNÉE (SOH.OrderDate),
DATEPART(MOIS, SOH.La requête de pivot dynamique
EXEC sp_executesql@dml
|
C’est tout pour l’instant
En conclusion
Jusqu’à présent, nous avons démontré les concepts de base et plusieurs exemples de PIVOT SQL et d’UNPIVOT SQL.
L’exemple est basé sur la base de données SQL Server adventureworks2014. Vous pouvez essayer et tester tous les échantillons vous-même. Si vous avez des questions, n’hésitez pas à commenter ci-dessous…
- Auteur
- Messages récents
Ma spécialité réside dans la conception de & implémentant des solutions haute disponibilité et une migration de base de données multiplateforme. Les technologies sur lesquelles travaillent actuellement sont SQL Server, PowerShell, Oracle et MongoDB.
Voir tous les messages de Prashanth Jayaram
- Un aperçu rapide de l’audit de base de données en SQL – 28 janvier 2021
- Comment configuration de la synchronisation des données Azure entre les bases de données SQL Azure et SQL Server sur site – 20 janvier 2021
- Comment effectuer des opérations d’importation/exportation de bases de données SQL Azure à l’aide de PowerShell – 14 janvier 2021