SQL Pivot Rows to Columns (lignes vers colonnes) : Un guide complet

Que signifie "pivoter des lignes vers des colonnes" en SQL ?

Le pivotage en SQL consiste à transformer des données d'un format basé sur des lignes en un format basé sur des colonnes. Cette transformation est utile pour l'établissement de rapports et l'analyse des données, car elle permet d'obtenir une vue plus structurée et plus compacte des données. Le pivotement des lignes vers les colonnes permet également aux utilisateurs d'analyser et de résumer les données d'une manière qui met plus clairement en évidence les idées clés.

Prenons l'exemple suivant : J'ai un tableau avec des transactions de vente quotidiennes, et chaque ligne enregistre la date, le nom du produit et le montant de la vente.

En pivotant ce tableau, je peux le restructurer pour présenter chaque produit sous forme de colonne, avec les données de vente pour chaque date sous la colonne correspondante. Notez également qu'une agrégation a lieu.

Traditionnellement, les opérations de pivot nécessitaient des requêtes SQL complexes avec une agrégation conditionnelle. Au fil du temps, les implémentations SQL ont évolué, de nombreuses bases de données modernes incluant désormais les opérateurs PIVOT et UNPIVOT pour permettre des transformations plus efficaces et plus directes.

Comprendre les lignes vers les colonnes de SQL Pivot

L'opération pivot SQL transforme les données en transformant les valeurs des lignes en colonnes. Voici la syntaxe et la structure de base du pivot SQL avec les parties suivantes :

• SELECT : L'instruction SELECT fait référence aux tableaux à retourner dans le tableau croisé dynamique SQL.

• Sous-requête : La sous-requête contient la source de données ou le tableau à inclure dans le tableau croisé dynamique SQL.

• PIVOT : L'opérateur PIVOT contient les agrégations et les filtres à appliquer dans le tableau croisé dynamique.

-- Select static columns and pivoted columns
SELECT <static columns>, [pivoted columns]
FROM
    (
        -- Subquery defining source data for pivot
        <subquery that defines data>
    ) AS source
PIVOT
(
    -- Aggregate function applied to value column, creating new columns
    <aggregation function>(<value column>)
    FOR <column to pivot> IN ([list of pivoted columns])
) AS pivot_table;

Examinons l'exemple suivant, étape par étape, pour démontrer comment faire pivoter des lignes vers des colonnes en SQL. Examinez le tableau SalesData ci-dessous.

Exemple de tableau à transformer à l'aide de l'opérateur SQL PIVOT. Image par l'auteur.

Je souhaite faire pivoter ces données pour comparer les ventes quotidiennes de chaque produit. Je commencerai par sélectionner la sous-requête qui structurera l'opérateur PIVOT.

-- Subquery defining source data for pivot
SELECT Date, Product, Sales
FROM SalesData;

Je vais maintenant utiliser l'opérateur PIVOT pour convertir les valeurs de Product en colonnes et agréger Sales à l'aide de l'opérateur SUM.

-- Select Date and pivoted columns for each product
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- Subquery to fetch Date, Product, and Sales columns
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- Aggregate Sales by Product, pivoting product values to columns
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

Exemple de transformation de sortie utilisant les lignes pivot SQL en colonnes. Image par l'auteur.

Bien que le pivotement des données simplifie la synthèse des données, cette technique présente des problèmes potentiels. Vous trouverez ci-dessous les difficultés potentielles liées au pivot SQL et la manière de les résoudre.

• Noms de colonnes dynamiques : Lorsque les valeurs à pivoter (par exemple, les types de produits) sont inconnues, le codage en dur des noms de colonnes ne fonctionne pas. Certaines bases de données, comme SQL Server, prennent en charge le SQL dynamique avec des procédures stockées pour éviter ce problème, tandis que d'autres requièrent un traitement au niveau de l'application.

• Traiter les valeurs NULL : Lorsqu'il n'y a pas de données pour une colonne pivotée spécifique, le résultat peut inclure NULL. Vous pouvez utiliser COALESCE pour remplacer les valeurs de NULL par zéro ou un autre caractère générique.

• Compatibilité entre les bases de données : Toutes les bases de données ne prennent pas directement en charge l'opérateur PIVOT. Vous pouvez obtenir des résultats similaires avec les instructions CASE et l'agrégation conditionnelle si votre dialecte SQL ne le permet pas.

SQL Pivot Rows to Columns (lignes vers colonnes) : Exemples et cas d'utilisation

Différentes méthodes sont utilisées pour pivoter les données en SQL, en fonction de la base de données utilisée ou d'autres exigences. Bien que l'opérateur PIVOT soit couramment utilisé dans SQL Server, d'autres techniques, telles que les instructions CASE, permettent des transformations similaires de la base de données sans support direct de PIVOT. Je vais vous présenter les deux méthodes courantes de pivotement des données en SQL, ainsi que leurs avantages et leurs inconvénients.

Utilisation de l'opérateur PIVOT

L'opérateur PIVOT, disponible dans SQL Server, offre un moyen simple de faire pivoter des lignes vers des colonnes en spécifiant une fonction d'agrégation et en définissant les colonnes à faire pivoter.

Considérez le tableau suivant, nommé sales_data.

Exemple Tableau d'ordres à transformer à l'aide de l'opérateur PIVOT. Image par l'auteur.

J'utiliserai l'opérateur PIVOT pour agréger les données de manière à ce que le total de sales_revenue pour chaque année apparaisse en colonnes.

-- Use PIVOT to aggregate sales revenue by year
SELECT *
FROM (
    -- Select the relevant columns from the source table
    SELECT sale_year, sales_revenue
    FROM sales_data
) AS src
PIVOT (
    -- Aggregate sales revenue for each year
    SUM(sales_revenue)
    -- Create columns for each year
    FOR sale_year IN ([2020], [2021], [2022], [2023])
) AS piv;

Exemple de transformation de sortie à l'aide de SQL PIVOT. Image par l'auteur.

L'utilisation de l'opérateur PIVOT présente les avantages et les limites suivants :

• Avantages : Cette méthode est efficace lorsque les colonnes sont correctement indexées. Sa syntaxe est également plus simple et plus lisible.

• Limites : Toutes les bases de données ne prennent pas en charge l'opérateur PIVOT. Il faut spécifier les colonnes à l'avance et le pivotement dynamique requiert une complexité supplémentaire.

Pivotement manuel avec les instructions CASE

Vous pouvez également utiliser les instructions CASE pour faire pivoter manuellement des données dans des bases de données qui ne prennent pas en charge les opérateurs PIVOT, comme MySQL et PostgreSQL. Cette approche utilise l'agrégation conditionnelle en évaluant chaque ligne et en attribuant conditionnellement des valeurs à de nouvelles colonnes sur la base de critères spécifiques.

Par exemple, nous pouvons faire pivoter manuellement les données du même tableau sales_data à l'aide des instructions CASE.

-- Aggregate sales revenue by year using CASE statements
SELECT 
    -- Calculate total sales revenue for each year
    SUM(CASE WHEN sale_year = 2020 THEN sales_revenue ELSE 0 END) AS sales_2020,
    SUM(CASE WHEN sale_year = 2021 THEN sales_revenue ELSE 0 END) AS sales_2021,
    SUM(CASE WHEN sale_year = 2022 THEN sales_revenue ELSE 0 END) AS sales_2022,
    SUM(CASE WHEN sale_year = 2023 THEN sales_revenue ELSE 0 END) AS sales_2023
FROM 
    sales_data;

Exemple de transformation de sortie à l'aide de l'instruction SQL CASE. Image par l'auteur.

L'utilisation de la déclaration CASE pour la transformation présente les avantages et les limites suivants :

• Avantages : Cette méthode fonctionne dans toutes les bases de données SQL et permet de générer dynamiquement de nouvelles colonnes, même lorsque les noms de produits sont inconnus ou changent fréquemment.

• Limites : Les requêtes peuvent devenir complexes et longues s'il y a de nombreuses colonnes à faire pivoter. En raison des multiples vérifications conditionnelles, la méthode est légèrement plus lente que l'opérateur PIVOT.

Considérations relatives aux performances lors du passage des lignes aux colonnes

La conversion de lignes en colonnes dans SQL peut avoir des conséquences sur les performances, en particulier lorsque vous travaillez avec de grands ensembles de données. Voici quelques conseils et bonnes pratiques qui vous aideront à rédiger des requêtes pivot efficaces, à optimiser leurs performances et à éviter les pièges les plus courants.

Meilleures pratiques

Voici les meilleures pratiques pour optimiser vos requêtes et améliorer les performances.

• Stratégies d'indexation : Une indexation correcte est essentielle pour optimiser les requêtes pivot, permettant à SQL d'extraire et de traiter les données plus rapidement. Indexez toujours les colonnes fréquemment utilisées dans la clause WHERE ou les colonnes que vous regroupez afin de réduire les temps de balayage.

• Évitez les pivots imbriqués : L'empilement de plusieurs opérations pivot dans une seule requête peut être difficile à lire et plus lent à exécuter. Simplifiez en divisant la requête en plusieurs tableaux ou en utilisant un tableau temporaire.

• Limiter les colonnes et les lignes dans le tableau croisé dynamique : Seules les colonnes pivotantes sont nécessaires à l'analyse, car le pivotement de nombreuses colonnes peut être gourmand en ressources et créer de grands tableaux.

Éviter les pièges les plus courants

Vous trouverez ci-dessous les erreurs les plus courantes que vous pouvez rencontrer dans les requêtes pivot et la manière de les éviter.

• Scanners inutiles du tableau complet : Les tableaux croisés dynamiques peuvent déclencher des balayages complets du tableau, en particulier si aucun index pertinent n'est disponible. Évitez les balayages complets du tableau en indexant les tableaux clés et en filtrant les données avant d'appliquer le pivot.

• Utilisation de SQL dynamique pour le pivotement fréquent : L'utilisation de SQL dynamique peut ralentir les performances en raison de la recompilation des requêtes. Pour éviter ce problème, mettez en cache ou limitez les pivots dynamiques à des scénarios spécifiques et envisagez de traiter les colonnes dynamiques dans la couche d'application lorsque cela est possible.

• Agrégation sur de grands ensembles de données sans pré-filtrage : Les fonctions d'agrégation telles que SUM ou COUNT sur de grands ensembles de données peuvent ralentir les performances de la base de données. Au lieu de faire pivoter l'ensemble des données, filtrez d'abord les données à l'aide d'une clause WHERE.

• Valeurs NULL dans les colonnes pivotantes : Les opérations de pivot produisent souvent des valeurs NULL lorsqu'il n'y a pas de données pour une colonne spécifique. Ces éléments peuvent ralentir les requêtes et rendre les résultats plus difficiles à interpréter. Pour éviter ce problème, utilisez des fonctions telles que COALESCE pour remplacer les valeurs de NULL par une valeur par défaut.

• Test avec des données d'échantillon uniquement : Les requêtes pivot peuvent se comporter différemment avec les grands ensembles de données en raison de l'augmentation de la mémoire et des exigences de traitement. Testez toujours les requêtes pivot sur des échantillons de données réels ou représentatifs afin d'évaluer avec précision l'impact sur les performances.

Essayez notre cursus de développeur SQL Server, qui couvre tous les domaines, des transactions à la gestion des erreurs en passant par l'amélioration des performances des requêtes.

Implémentations spécifiques aux bases de données

Les opérations de pivot diffèrent considérablement d'une base de données à l'autre (SQL Server, MySQL, Oracle, etc.). Chacune de ces bases de données a une syntaxe et des limitations spécifiques. Je vous présenterai des exemples de pivotement de données dans les différentes bases de données et leurs principales caractéristiques.

Serveur SQL

SQL Server dispose d'un opérateur PIVOT intégré, qui permet de faire pivoter des lignes vers des colonnes. L'opérateur PIVOT est facile à utiliser et s'intègre aux puissantes fonctions d'agrégation de SQL Server. Les principales caractéristiques du pivotement dans SQL sont les suivantes :

• Soutien direct à PIVOT et UNPIVOT : L'opérateur PIVOT de SQL Server permet de transformer rapidement une ligne en une colonne. L'opérateur UNPIVOT peut également inverser ce processus.

• Options d'agrégation : L'opérateur PIVOT permet diverses fonctions d'agrégation, telles que SUM, COUNT, et AVG.

La limite de l'opérateur PIVOT dans SQL Server est qu'il nécessite que les valeurs des colonnes à pivoter soient connues à l'avance, ce qui le rend moins flexible pour les données qui changent dynamiquement.

Dans l'exemple ci-dessous, l'opérateur PIVOT convertit les valeurs de Product en colonnes et agrège Sales à l'aide de l'opérateur SUM.

-- Select Date and pivoted columns for each product
SELECT Date, [Laptop], [Mouse] 
FROM
    (
        -- Subquery to fetch Date, Product, and Sales columns
        SELECT Date, Product, Sales FROM SalesData
    ) AS source
PIVOT
(
    -- Aggregate Sales by Product, pivoting product values to columns
    SUM(Sales)
    FOR Product IN ([Laptop], [Mouse])
) AS pivot_table;

Je vous recommande de suivre le cours Introduction à SQL Server de DataCamp pour maîtriser les bases de SQL Server pour l'analyse de données.

MySQL

MySQL ne prend pas en charge l'opérateur PIVOT. Cependant, vous pouvez utiliser l'instruction CASE pour faire pivoter manuellement les lignes vers les colonnes et combiner d'autres fonctions d'agrégation comme SUM, AVG et COUNT. Bien que cette méthode soit flexible, elle peut devenir complexe si vous avez de nombreuses colonnes à faire pivoter.

La requête ci-dessous permet d'obtenir le même résultat que l'exemple du serveur SQL PIVOT en agrégeant de manière conditionnelle les ventes de chaque produit à l'aide de l'instruction CASE.

-- Select Date and pivoted columns for each product
SELECT 
    Date,
    -- Use CASE to create a column for Laptop and Mouse sales
    SUM(CASE WHEN Product = 'Laptop' THEN Sales ELSE 0 END) AS Laptop,
    SUM(CASE WHEN Product = 'Mouse' THEN Sales ELSE 0 END) AS Mouse
FROM SalesData
GROUP BY Date;

Oracle

Oracle prend en charge l'opérateur PIVOT, qui permet de transformer directement des lignes en colonnes. Tout comme pour SQL Server, vous devrez spécifier explicitement les colonnes à transformer.

Dans la requête ci-dessous, l'opérateur PIVOT convertit les valeurs de ProductName en colonnes et agrège SalesAmount à l'aide de l'opérateur SUM.

SELECT *
FROM (
    -- Source data selection
    SELECT SaleDate, ProductName, SaleAmount FROM SalesData
)
PIVOT (
    -- Aggregate Sales by Product, creating pivoted columns
    SUM(SaleAmount)
    FOR ProductName IN ('Laptop' AS Laptop, 'Mouse' AS Mouse)
);

Exemple de transformation de sortie utilisant l'opérateur SQL PIVOT dans Oracle. Image par l'auteur.

Techniques avancées de pivotement des lignes vers les colonnes en SQL

Les techniques avancées de pivotement des lignes en colonnes sont utiles lorsque vous avez besoin de souplesse dans le traitement de données complexes. Les techniques dynamiques et le traitement simultané de plusieurs colonnes vous permettent de transformer les données dans des scénarios où le pivotement statique est limité. Examinons ces deux méthodes en détail.

Pivots dynamiques

Les pivots dynamiques vous permettent de créer des requêtes pivots qui s'adaptent automatiquement aux modifications des données. Cette technique est particulièrement utile lorsque vous avez des colonnes qui changent fréquemment, comme les noms de produits ou les catégories, et que vous souhaitez que votre requête inclue automatiquement les nouvelles entrées sans avoir à la mettre à jour manuellement.

Supposons que nous disposions d'un tableau SalesData et que nous puissions créer un pivot dynamique qui s'ajuste si de nouveaux produits sont ajoutés. Dans la requête ci-dessous, @columns construit dynamiquement la liste des colonnes pivotantes et sp_executesql exécute le code SQL généré.

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
-- Step 1: Generate a list of distinct products to pivot
SELECT @columns = STRING_AGG(QUOTENAME(Product), ', ') 
FROM (SELECT DISTINCT Product FROM SalesData) AS products;
-- Step 2: Build the dynamic SQL query
SET @sql = N'
SELECT Date, ' + @columns + '
FROM 
    (SELECT Date, Product, Sales FROM SalesData) AS source
PIVOT
(
    SUM(Sales)
    FOR Product IN (' + @columns + ')
) AS pivot_table;';
-- Step 3: Execute the dynamic SQL
EXEC sp_executesql @sql;

Gestion de plusieurs colonnes

Dans les cas où vous devez faire pivoter plusieurs colonnes simultanément, vous utiliserez l'opérateur PIVOT et d'autres techniques d'agrégation pour créer plusieurs colonnes dans la même requête. 

Dans l'exemple ci-dessous, j'ai fait pivoter les colonnes Sales et Quantity par Product.

-- Pivot Sales and Quantity for Laptop and Mouse by Date
SELECT 
    p1.Date, 
    p1.[Laptop] AS Laptop_Sales, 
    p2.[Laptop] AS Laptop_Quantity, 
    p1.[Mouse] AS Mouse_Sales, 
    p2.[Mouse] AS Mouse_Quantity
FROM 
    (
        -- Pivot for Sales
        SELECT Date, [Laptop], [Mouse]
        FROM 
            (SELECT Date, Product, Sales FROM SalesData) AS source
        PIVOT
            (SUM(Sales) FOR Product IN ([Laptop], [Mouse])) AS pivot_sales
    ) p1
JOIN
    (
        -- Pivot for Quantity
        SELECT Date, [Laptop], [Mouse]
        FROM 
            (SELECT Date, Product, Quantity FROM SalesData) AS source
        PIVOT
            (SUM(Quantity) FOR Product IN ([Laptop], [Mouse])) AS pivot_quantity
    ) p2
ON p1.Date = p2.Date;

Exemple de transformation de plusieurs colonnes à l'aide de l'opérateur SQL PIVOT. Image par l'auteur.

Le pivotement de plusieurs colonnes permet d'obtenir des rapports plus détaillés en faisant pivoter plusieurs attributs par élément, ce qui permet d'obtenir des informations plus riches. Cependant, la syntaxe peut être complexe, surtout s'il existe de nombreuses colonnes. Un codage en dur peut être nécessaire s'il n'est pas combiné à des techniques de pivot dynamique, ce qui ajoute encore à la complexité.

Conclusion

Le pivotement des lignes vers les colonnes est une technique SQL qui mérite d'être apprise. J'ai vu des techniques de pivot SQL utilisées pour créer un tableau de rétention de cohorte, dans lequel vous pourriez suivre la rétention des utilisateurs au fil du temps. J'ai également vu des techniques de pivot SQL utilisées lors de l'analyse de données d'enquête, où chaque ligne représente un répondant et où chaque question peut être pivotée dans sa colonne. 

Notre cours Reporting in SQL est une excellente option si vous souhaitez en savoir plus sur le résumé et la préparation des données pour la présentation et/ou la création de tableaux de bord. Nos cursus Associate Data Analyst in SQL et Associate Data Engineer in SQL sont une autre excellente idée, et ils ajoutent beaucoup à n'importe quel CV, alors inscrivez-vous dès aujourd'hui.