Exemple de Requête PIVOT SQL Server

26 février 2024

Comment construire une requête PIVOT SQL Server pour transformer des lignes en colonnes ?

Suivez ce tutoriel et ces exemples de requêtes sur comment utiliser l’opérateur PIVOT de SQL Server pour convertir des lignes de données en colonnes. L’opérateur Pivot de SQL Server permet de transposer des lignes en colonnes. Cependant, la syntaxe n’est pas simple, surtout pour les débutants.

En effet, pour fonctionner, les noms des colonnes cibles doivent être fournis. Et ils doivent correspondre au contenu de la colonne pivotée. Cet exemple simple de requête Pivot montre comment construire et adapter votre propre requête étape par étape. Elle déplace simplement les lignes contenant les noms des mois en colonnes tout en calculant la moyenne des ventes pour chaque mois.

Requêtes pivot et agrégation de données

D’un autre côté, si nous ne voulons pas d’agrégation dans les nouvelles lignes de résultats, alors nous avons besoin exactement d’une ligne par colonne créée. Dans l’exemple ci-dessous, nous faisons un pivot avec une agrégation et nous utilisons la fonction moyenne.

Et seulement les six premiers mois de l’année sont utilisés et pivotés, à savoir de janvier à juin. Il est facile d’étendre jusqu’à la fin de l’année en ajoutant les 6 mois suivants. Pour ce faire, il suffit de copier/coller la création de données et la requête, et d’ajouter les mois manquants.

1. Requête Pivot SQL Server avec colonne fixe unique

étape 1.1 : Créer une table exemple pour transposer de lignes en colonnes

Avant de construire la requête, créez la table exemple avec ce script T-SQL, copiez et collez-le simplement dans votre fenêtre SSMS.

-- Si la table existe alors elle est supprimée
IF exists(    SELECT    1 FROM sys.objects
            WHERE    object_id = object_id(N'[dbo].[SALES]') 
                AND type in (N'U') )
BEGIN    
    DROP TABLE [dbo].[SALES]
END
GO

-- Créer la table des ventes à pivoter
CREATE table [dbo].[SALES] (
    [MONTH] NVARCHAR(20),
    [AMOUNT] NUMERIC(5)
)
GO

-- Insérer le premier montant mensuel
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'January', 1000)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'February', 2000)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'March', 3000)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'April', 4000)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'May', 5000)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'June', 6000)

-- Insérer le deuxième montant mensuel
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'January', 1100)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'February', 2200)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'March', 3300)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'April', 4400)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'May', 5500)
INSERT INTO dbo.SALES ( MONTH, AMOUNT ) VALUES ( N'June', 6600)

-- Sélectionner les données insérées
SELECT    *
FROM    dbo.SALES;

étape 1.2 : Construire et exécuter la requête PIVOT SQL Server

La requête pour transformer des lignes en colonnes est composée de ces trois parties, elle calcule les ventes moyennes par mois :

  1. Une sélection de la colonne agrégée et des mois, chaque colonne appelée explicitement.
  2. La sous-requête avec la sélection originale des données.
  3. Le PIVOT lui-même utilisant la fonction d’agrégation AVG.
SELECT    'Average' AS [SALES_PER_MONTH],
        [January], [February], [March], [April], [May], [June]
FROM (
    SELECT [MONTH], [AMOUNT]
    FROM dbo.SALES
) AS SourceTable
PIVOT (
    AVG(AMOUNT)
    FOR MONTH IN ([January], [February], [March], [April], [May],[June])
) AS PivotTable;

Le résultat de la requête apparaît en colonnes après l’utilisation du PIVOT.

Requête PIVOT SQL Server pour transposer des lignes en colonnes
Requête PIVOT SQL Server pour transposer des lignes en colonnes

Pour effectuer l’opération inverse, c’est-à-dire transformer les colonnes en lignes, utiliser l’opérateur UNPIVOT de SQL Server.

SQL Server UNPIVOT Example

2. Requête Pivot SQL Server avec plusieurs colonnes fixes

Dans ce second exemple, la table à pivoter a 2 colonnes non numériques fixes. C’est-à-dire que les deux colonnes ne seront ni agrégées ni calculées et elles ne sont pas non plus pivotées.

étape 2.1 : Script pour créer une table avec deux ou plusieurs colonnes fixes

Ici, nous utilisons le même processus que dans le premier exemple. Mais cette fois, nous regroupons les données différemment. Ouvrez SSMS et exécutez cette requête pour créer la table et insérer des données exemple. En effet, nous avons simplement ajouté les ID et noms des clients comme 2 nouvelles colonnes.

-- Créer la table des ventes avec 2 colonnes supplémentaires
-- L'ID du client et le nom du client
CREATE table [dbo].[SALES_with_Customers] (
    [MONTH]         NVARCHAR(20),
    [CUSTOMER_ID]   NVARCHAR(20),
    [CUSTOMER_NAME] NVARCHAR(20),
    [AMOUNT]        NUMERIC(5)
)
GO

-- Insérer le premier montant des ventes pour chaque mois pour Customer-001
INSERT dbo.SALES_with_Customers VALUES ( N'January',  N'Customer-001', N'ONE', 1000)
INSERT dbo.SALES_with_Customers VALUES ( N'February', N'Customer-001', N'ONE', 2000)
INSERT dbo.SALES_with_Customers VALUES ( N'March',    N'Customer-001', N'ONE', 3000)
INSERT dbo.SALES_with_Customers VALUES ( N'April',    N'Customer-001', N'ONE', 4000)
INSERT dbo.SALES_with_Customers VALUES ( N'May',      N'Customer-001', N'ONE', 5000)
INSERT dbo.SALES_with_Customers VALUES ( N'June',     N'Customer-001', N'ONE', 6000)
-- deuxième étape pour le premier client
INSERT dbo.SALES_with_Customers VALUES ( N'January',  N'Customer-001', N'ONE', 1500)
INSERT dbo.SALES_with_Customers VALUES ( N'February', N'Customer-001', N'ONE', 1500)
INSERT dbo.SALES_with_Customers VALUES ( N'March',    N'Customer-001', N'ONE', 1500)
INSERT dbo.SALES_with_Customers VALUES ( N'April',    N'Customer-001', N'ONE', 1500)
INSERT dbo.SALES_with_Customers VALUES ( N'May',      N'Customer-001', N'ONE', 1500)
INSERT dbo.SALES_with_Customers VALUES ( N'June',     N'Customer-001', N'ONE', 1500)

-- Insérer le premier montant des ventes pour chaque mois pour Customer-002
INSERT dbo.SALES_with_Customers VALUES ( N'January',  N'Customer-002', N'TWO', 1100)
INSERT dbo.SALES_with_Customers VALUES ( N'February', N'Customer-002', N'TWO', 2200)
INSERT dbo.SALES_with_Customers VALUES ( N'March',    N'Customer-002', N'TWO', 3300)
INSERT dbo.SALES_with_Customers VALUES ( N'April',    N'Customer-002', N'TWO', 4400)
INSERT dbo.SALES_with_Customers VALUES ( N'May',      N'Customer-002', N'TWO', 5500)
INSERT dbo.SALES_with_Customers VALUES ( N'June',     N'Customer-002', N'TWO', 6600)
-- deuxième étape pour le deuxième client
INSERT dbo.SALES_with_Customers VALUES ( N'January',  N'Customer-002', N'TWO', 2000)
INSERT dbo.SALES_with_Customers VALUES ( N'February', N'Customer-002', N'TWO', 2000)
INSERT dbo.SALES_with_Customers VALUES ( N'March',    N'Customer-002', N'TWO', 2000)
INSERT dbo.SALES_with_Customers VALUES ( N'April',    N'Customer-002', N'TWO', 2000)
INSERT dbo.SALES_with_Customers VALUES ( N'May',      N'Customer-002', N'TWO', 2000)
INSERT dbo.SALES_with_Customers VALUES ( N'June',     N'Customer-002', N'TWO', 2000)

-- Vérifier les données insérées
SELECT  *
FROM    dbo.[SALES_with_Customers];

Noter que la table source contient 12 lignes par ID et nom de client, pour les 6 premiers mois de l’année.

Requête dans SSMS pour créer la table avec plusieurs colonnes fixes à pivoter
Requête dans SSMS pour créer la table avec plusieurs colonnes fixes à pivoter

étape 2.2 Construire la requête PIVOT avec plusieurs colonnes fixes

Une fois la nouvelle table créée, construisez la requête pivot. La même méthode est utilisée ici.

La seule différence est que toutes les colonnes nommées sont listées dans la première instruction select.

SELECT    *
FROM (
    SELECT
     [MONTH],
     [CUSTOMER_ID],
     [CUSTOMER_NAME],
     [AMOUNT]
    FROM dbo.SALES_with_Customers
) AS Source_Table

PIVOT (
    AVG(AMOUNT)
    FOR MONTH IN ([January], [February], [March], [April], [May], [June])
) AS Pivot_Table;

Construisez la requête en utilisant les trois mêmes étapes.

  1. De la table source, sélectionnez toutes les colonnes à pivoter et à afficher : Mois, Customer_Id, Customer_Name et Amount.
  2. Configurez la colonne de pivot et l’agrégation, ici c’est AVG(AMOUNT).
  3. Utilisez l’opérateur PIVOT pour exécuter la transformation sur les données sélectionnées.
Requête SQL Server PIVOT expliquée
Requête SQL Server PIVOT expliquée

Finalement exécutez la requête et vérifiez la moyenne calculée de la colonne des ventes, affichée dans la colonne Amount.

  1. Les données du premier client sont maintenant disponibles en une seule ligne.
  2. Il en va de même pour Customer-002.
  3. Chaque mois est maintenant affiché dans une colonne dédiée spécifique.
Table des ventes avant et après le PIVOT des lignes en colonnes avec SQL Server
Table des ventes avant et après le PIVOT des lignes en colonnes avec SQL Server

L’utilisation de la requête PIVOT SQL Server est utile

Pour finir, ce tutoriel T-SQL explique comment utiliser l’opérateur PIVOT dans SQL Server avec 2 exemples étape par étape. Pour aller plus loin et interroger les métadonnées des tables systèmes, utilisez la requête pour afficher la date et l’heure de la dernière mise à jour d’une table SQL Server.

Plus de tutoriels pour Pivoter des données

Soyez le premier à commenter

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée.


*