Syntaxe de la requête PIVOT SQL Server pour transposer les colonnes d’une table en lignes.
La requête SQL Server PIVOT permet de transformer des lignes en colonnes. Cet exemple de requête simple pivote des lignes d’une table de ventes avec des mois en lignes qui passent en colonnes. Pour utiliser la requête SQL Server Pivot, sélectionner la moyenne des ventes et des mois, construire la sous-requête des données de la table source, et enfin, utiliser le mot clef PIVOT qui transpose les lignes en colonnes en précisant quelle opération d’agrégation utiliser.
Table des matières
1. Comment écrire une requête PIVOT avec SQL Server ?
Cet exemple de requête PIVOT SQL Server montre comment construire la requête pas à pas, et permet de transposer les lignes contenant les mois en colonnes. Tout en calculant la moyenne du montant des ventes associées à chaque mois.
De plus, dans cet exemple de PIVOT SQL, seuls les 6 premiers mois de l’année sont présents et transposés, c’est-à-dire janvier à juin, mais il est facile d’extrapoler et avoir plus de colonnes, pour cela il suffit de copier / coller l’exemple en bas de page et de changer la liste de mois. En effet, ajouter les 6 derniers mois de l’année et vous aurez toute l’année dans votre requête. Un cas pratique d’entreprise aura forcément 12 mois.
2. Créer la table d’exemple avec les colonnes à pivoter en lignes
Avant d’exécuter la commande, créer la table d’exemple MOIS avec le script SQL de création de la table des Ventes.
2.1 Créer la table des ventes à transformer
Depuis SSMS, exécuter ce code pour créer la table des ventes. Cette table, très basique, comporte seulement 2 colonnes:
- Une colonne Mois pour stocker le mois des ventes.
- Une deuxième colonne, Montant pour stocker le montant des ventes.
-- Créer la table des ventes à transposer CREATE table [dbo].[VENTES] ( [MOIS] nvarchar(20), [MONTANT] numeric(5) ) GO
2.2 Insérer les données à transposer dans la table
Exécuter maintenant la partie d’insertions des données, ce script permet d’insérer 12 lignes, avec 2 lignes par mois. Et de lire le contenu de la table.
-- Insertion des données de ventes pour notre exemple INSERT INTO dbo.VENTES ( MOIS, MONTANT ) VALUES ( N'Janvier', 1000) INSERT INTO dbo.VENTES ( MOIS, MONTANT ) VALUES ( N'Février', 2000) INSERT INTO dbo.VENTES ( MOIS, MONTANT ) VALUES ( N'Mars', 3000) INSERT INTO dbo.VENTES ( MOIS, MONTANT ) VALUES ( N'Avril', 4000) INSERT INTO dbo.VENTES ( MOIS, MONTANT ) VALUES ( N'Mai', 5000) INSERT INTO dbo.VENTES ( MOIS, MONTANT ) VALUES ( N'Juin', 6000) -- Insertion du deuxième montant pour chaque mois INSERT INTO dbo.VENTES ( MOIS, MONTANT ) VALUES ( N'Janvier', 1100) INSERT INTO dbo.VENTES ( MOIS, MONTANT ) VALUES ( N'Février', 2200) INSERT INTO dbo.VENTES ( MOIS, MONTANT ) VALUES ( N'Mars', 3300) INSERT INTO dbo.VENTES ( MOIS, MONTANT ) VALUES ( N'Avril', 4400) INSERT INTO dbo.VENTES ( MOIS, MONTANT ) VALUES ( N'Mai', 5500) INSERT INTO dbo.VENTES ( MOIS, MONTANT ) VALUES ( N'Juin', 6600) -- Vérification des montants en lignes mois par mois SELECT * FROM dbo.VENTES;
3. Requête SQL PIVOT pour transformer des colonnes en lignes
En effet, la requête PIVOT présentée dessous fait une moyenne des Ventes par Mois au départ en lignes qu’elle affiche ensuite en Colonne, elle se compose de 3 parties :
- Premièrement, la sélection de la moyenne des ventes et des mois.
- Ensuite, la sous-requête des données de la table source à pivoter : dbo.VENTES
- Enfin, le pivot des colonnes en lignes en précisant que la colonne MONTANT devient la moyenne des Mois.
SELECT 'Moyenne des Ventes' AS [Ventes_Par_Mois], [Janvier], [Février], [Mars], [Avril], [Mai], [Juin] FROM ( SELECT MOIS, MONTANT FROM dbo.VENTES ) AS TableSource PIVOT ( AVG(MONTANT) FOR MOIS IN ([Janvier], [Février], [Mars], [Avril], [Mai],[Juin]) ) AS TableDePivot;
Voici la documentation officielle de Microsoft sur l’utilisation des opérateurs PIVOT et UNPIVOT. Enfin, cet exemple de PIVOT SQL Server simple en 3 étapes montre que malgré la syntaxe non intuitive de cette fonction, il est possible de l’utiliser et de l’adapter à ses besoins.
4. Transposer des données de lignes en colonnes avec UNPIVOT
Pour aller plus loin, la requête PIVOT est l’inverse de la fonction UNPIVOT. Cette dernière permet de transposer cette fois les lignes en colonnes, voici un exemple de requête SQL UNPIVOT.
