Accéder au contenu principal

Comment utiliser SQL PIVOT

Améliorez vos compétences SQL avec l'opérateur SQL PIVOT. Apprenez à convertir des lignes en colonnes pour créer des tableaux croisés dynamiques dans SQL Server et Oracle.
Actualisé 16 janv. 2025  · 10 min de lecture

L'opérateur PIVOT dans SQL Server et Oracle est une technique extrêmement utile qui transforme les tableaux en colonnes. Non seulement l'opérateur PIVOT améliore la lisibilité et l'interprétation des résultats des requêtes, mais il facilite également la compréhension des tendances des données en utilisant les agrégations pour créer des tableaux croisés ou des tableaux pivotants. Ces tableaux croisés dynamiques sont particulièrement utiles dans les rapports qui nécessitent de belles visualisations.

Avant de commencer, je vous recommande de consulter le cursus SQL Fundamentals de DataCamp si vous avez l'impression que vos compétences en SQL sont rouillées. Notre cursus de compétences SQL Fundamentals vous aidera à comprendre comment joindre et manipuler des données, ainsi qu'à utiliser des sous-requêtes et des fonctions de fenêtre.

La réponse rapide : Comment créer un pivot en SQL

L'opérateur SQL Server PIVOT est utile pour résumer des données car il permet de transformer des lignes en colonnes. Examinez le tableau city_sales ci-dessous, qui indique les ventes générales d'un produit dans cinq grandes villes des États-Unis.

Exemple de tableau à transformer à l'aide de SQL PIVOT

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

Nous allons utiliser la requête suivante, qui utilise l'opérateur PIVOT, pour faire pivoter plusieurs colonnes dans le tableau ci-dessus.

-- Select the columns for the output: city and sales data for 2019, 2020, and 2021
SELECT 
    city,
    [2019] AS Sales_2019,
    [2020] AS Sales_2020,
    [2021] AS Sales_2021
FROM
    (
        -- Subquery to select city, year, and sales from city_sales table
        SELECT city, year, sales 
        FROM city_sales
    ) AS src
PIVOT
(
    -- Pivot the sales data to have years as columns and sum the sales for each year
    SUM(sales)
    FOR year IN ([2019], [2020], [2021])
) AS pvt;

Exemple de transformation de sortie à l'aide de SQL PIVOT

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

Qu'est-ce que PIVOT en SQL ?

Le pivotage est une technique SQL utilisée pour transformer des lignes en colonnes dans des données tabulaires. Dans SQL Server et Oracle, le pivotement se fait avec l'opérateur PIVOT. La syntaxe de l'opérateur SQL PIVOT, présentée ci-dessous, comporte trois parties principales :

  • 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 the non-pivoted column and the pivoted columns with aliases
SELECT 
    [non-pivoted column],               
    [first pivoted column] AS [column name],   
    [second pivoted column] AS [column name],  
    ...
FROM
    (
        -- Subquery to select the necessary columns from the source table
        SELECT [columns] 
        FROM [source_table]
    ) AS source_table
PIVOT
(
    -- Pivot operation to aggregate data and transform rows into columns
    [aggregate_function]([pivot_column])        
    FOR [pivot_column] IN ([first pivoted column], [second pivoted column], ...) 
) AS pivot_table; -- Alias for the result of the pivot operation

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

Les bases de données SQL Server et Oracle prennent directement en charge l'opérateur PIVOT. Cependant, MySQL et PostgreSQL disposent d'autres méthodes pour créer des tableaux croisés dynamiques en SQL.

PIVOT dans le serveur SQL

SQL Server prend en charge l'opérateur PIVOT en mode natif. Ici, nous utiliserons l'opérateur PIVOT pour transformer les lignes en colonnes et résumer les données à l'aide de fonctions d'agrégation telles que SUM(). Nous utiliserons également des clauses SQL, telles que WHERE, GROUP BY, et ORDER BY pour une manipulation plus fine des données.

L'exemple ci-dessous montre comment utiliser l'opérateur PIVOT pour filtrer les données de l'année 2020 ou ultérieure (WHERE), regrouper les données par ville et année (GROUP BY) et trier les données par ville (ORDER BY) :

-- Select the city and sales data for the years 2019, 2020, and 2021
SELECT 
    city,                         
    [2019] AS Sales_2019,             
    [2020] AS Sales_2020,           
    [2021] AS Sales_2021              
FROM
    (
        -- Subquery to select city, year, and sales from the city_sales table
        SELECT city, year, sales 
        FROM city_sales
        WHERE year >= 2020 -- filtering
        GROUP BY city, year, sales -- grouping
    ) AS src                   
PIVOT
(
    -- Pivot the sales data to have years as columns, averaging the sales over each year
    SUM(sales) -- aggregating
    FOR year IN ([2019], [2020], [2021]) 
) AS pvt;         

Exemple de résultat d'un tableau transformé à l'aide de SQL PIVOT avec les clauses WHERE, GROUP BY et ORDER BY

Exemple de sortie d'un tableau transformé à l'aide de SQL PIVOT avec des clauses communes. Image par l'auteur.

PIVOT dans Oracle

Comme SQL Server, Oracle utilise également l'opérateur PIVOT pour transformer les lignes en colonnes. Cependant, la syntaxe de l'opérateur PIVOT dans la base de données Oracle diffère légèrement de celle de SQL Server. La requête ci-dessous montre comment l'opérateur PIVOT apparaît dans Oracle. Notez que les colonnes sont aliasées dans l'opérateur PIVOT, contrairement à l'instruction externe SELECT dans SQL Server.

-- Outer SELECT to choose all columns resulting from the PIVOT operation
SELECT *
FROM (
    -- Inner SELECT to retrieve the raw data of city, year, and sales
    SELECT city, year, sales
    FROM sales
)
-- PIVOT operation to convert rows to columns
PIVOT (
    SUM(sales) 
    -- Specify the year values to pivot and alias them as Sales_<year>
    FOR year IN (2019 AS Sales_2019, 2020 AS Sales_2020, 2021 AS Sales_2021)
)
ORDER BY city;

Pivoter dans MySQL

La base de données MySQL ne prend pas en charge l'opérateur SQL PIVOT. Pour créer des tableaux croisés dynamiques SQL dans MySQL, vous devez utiliser l'instruction CASE avec l'agrégation conditionnelle. Par exemple, la requête ci-dessous créera un tableau croisé dynamique pour agréger les données par la somme des ventes pour différentes années, regroupées et ordonnées par city.

-- Select the city and sum the sales data for the years 2019, 2020, and 2021
SELECT 
    city,
    SUM(CASE WHEN year = 2019 THEN sales ELSE 0 END) AS Sales_2019,
    SUM(CASE WHEN year = 2020 THEN sales ELSE 0 END) AS Sales_2020,
    SUM(CASE WHEN year = 2021 THEN sales ELSE 0 END) AS Sales_2021
FROM 
    city_sales 
GROUP BY 
    city
ORDER BY 
    city; 

Pivot dans PostgreSQL

La base de données PostgreSQL ne prend pas non plus en charge l'opérateur SQL PIVOT. Par conséquent, lorsque vous créez des tableaux croisés dynamiques, il est important d'utiliser l'instruction CASE avec une agrégation conditionnelle. La requête ci-dessous est un exemple des instructions conditionnelles CASE utilisées pour créer des tableaux croisés dynamiques dans PostgreSQL.

-- Select the city and sum the sales data for the years 2019, 2020, and 2021
SELECT 
    city,
    SUM(CASE WHEN year = 2019 THEN sales ELSE 0 END) AS Sales_2019, 
    SUM(CASE WHEN year = 2020 THEN sales ELSE 0 END) AS Sales_2020,
    SUM(CASE WHEN year = 2021 THEN sales ELSE 0 END) AS Sales_2021
FROM 
    city_sales
GROUP BY 
    city
ORDER BY 
    city; 

Techniques avancées avec SQL PIVOT

Il existe des techniques avancées de pivotement SQL permettant d'écrire des requêtes complexes. Dans cette section, nous nous intéresserons au pivotement dynamique, que nous utiliserons à l'adresse pour créer des requêtes pour des tableaux croisés dynamiques dont les colonnes à pivoter sont inconnues. Cette méthode utilise SQL pour générer le tableau croisé dynamique au moment de l'exécution.

PIVOT dynamique dans SQL Server

La requête ci-dessous utilise PIVOT pour faire pivoter dynamiquement la colonne year dans le serveur SQL. La requête permettra à d'extraire des années distinctes du tableau city_sales. Il va ensuite construire et exécuter une requête dynamique PIVOT en utilisant les années récupérées.

-- Declare variables to hold the column names and the dynamic query
DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);
-- Get distinct values of the year column and concatenate them into a string
SELECT @cols = STRING_AGG(QUOTENAME(year), ',') 
               FROM (SELECT DISTINCT year FROM city_sales) AS years;
-- Construct the dynamic PIVOT query
SET @query = '
SELECT city, ' + @cols + '
FROM
(
    -- Subquery to select city, year, and sales from the city_sales table
    SELECT city, year, sales
    FROM city_sales
) AS src
PIVOT
(
    -- Pivot the sales data to have years as columns, summing the sales for each year
    SUM(sales)
    FOR year IN (' + @cols + ')
) AS pvt
ORDER BY city';  -- Order the results by city
-- Execute the dynamic PIVOT query
EXEC sp_executesql @query;

Exemple de sortie d'un tableau utilisant un PIVOT dynamique SQL

Exemple de sortie d'un tableau utilisant le PIVOT dynamique SQL. Image par l'auteur.

PIVOT dynamique dans Oracle

Dans la base de données Oracle, le pivotement dynamique est possible en exécutant la requête dynamique à l'aide de l'instruction EXECUTE IMMEDIATE. La fonction LISTAGG est également utilisée pour agréger dynamiquement les noms de colonnes et les guillemets simples ' ' utilisés dans les alias au sein du pivot.

DECLARE
    cols VARCHAR2(4000);
    sql_query VARCHAR2(4000);
BEGIN
    -- Get the list of years dynamically
    SELECT LISTAGG('''' || year || ''' AS ' || 'sales_' || year, ',') 
    INTO cols
    FROM (SELECT DISTINCT year FROM city_sales);
    -- Construct the dynamic SQL query
    sql_query := 'SELECT * FROM (
                      SELECT city, year, sales
                      FROM city_sales
                  )
                  PIVOT (
                      SUM(sales)
                      FOR year IN (' || cols || ')
                  )
                  ORDER BY city';
    -- Execute the dynamic SQL query
    EXECUTE IMMEDIATE sql_query;
END;

Pivot dynamique dans MySQL

MySQL ne prend pas en charge le SQL dynamique direct. Vous devez donc créer une procédure stockée pour PIVOT dynamique dans MySQL. La requête ci-dessous montre comment utiliser la procédure stockée pour créer une requête dynamique PIVOT.

-- Declare variables to hold the dynamic columns (cols) and the final SQL query
DELIMITER $
CREATE PROCEDURE dynamic_pivot()
BEGIN
    DECLARE cols VARCHAR(1000);
    DECLARE sql_query VARCHAR(2000);
    -- Get the list of distinct years
    SELECT GROUP_CONCAT(DISTINCT
        CONCAT('SUM(CASE WHEN year = ', year, ' THEN sales ELSE 0 END) AS ', year, '')
    ) INTO cols
    FROM city_sales;
    -- Construct the dynamic SQL query
    SET sql_query = CONCAT('SELECT city, ', cols, ' FROM city_sales GROUP BY city ORDER BY city');
    -- Prepare and execute the SQL query
    PREPARE stmt FROM sql_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $
DELIMITER ;

Après avoir créé la procédure stockée, vous devez l'appeler pour exécuter la requête dynamique PIVOT:

CALL dynamic_pivot();

Pivot dynamique dans PostgreSQL

De la même manière, vous pouvez créer un site dynamique PIVOT dans PostgreSQL à l'aide de la requête suivante :

-- Block declaration to execute PL/pgSQL code in an anonymous code block
DO
$
DECLARE
    cols text;  -- Variable to store the list of columns for the dynamic query
    query text; -- Variable to store the dynamic SQL query
BEGIN
    -- Get distinct years and construct the list of SUM(CASE...) statements
    SELECT STRING_AGG(DISTINCT 'SUM(CASE WHEN year = ' || year || ' THEN sales ELSE 0 END) AS "Sales_' || year || '"', ', ')
    INTO cols
    FROM city_sales;
    -- Construct the dynamic PIVOT query
    query := 'SELECT city, ' || cols || ' FROM city_sales GROUP BY city ORDER BY city';
    -- Execute the dynamic PIVOT query
    EXECUTE query;
END
$;

Conclusion et formation complémentaire

Il est important de comprendre comment utiliser PIVOT dans SQL si vous voulez transformer et analyser des données de manière efficace. Lorsque vous créez des tableaux croisés dynamiques en SQL, il est essentiel de connaître les différentes implémentations de l'opérateur PIVOT dans les différentes bases de données. En tant qu'analyste de données, je vous encourage à continuer à pratiquer vos compétences SQL pour apprendre comment et quand appliquer PIVOT pour analyser différents ensembles de données.

Si vous êtes un analyste de données en herbe cherchant à mettre un pied dans le secteur ou un analyste plus chevronné, je vous recommande de suivre les cours Introduction à SQL et SQL intermédiaire de DataCamp pour améliorer vos compétences en matière d'analyse de données. Je vous recommande également de suivre notre cours Manipulation de données en SQL, qui enseigne les sous-requêtes et d'autres concepts abordés dans ce tutoriel, ainsi que notre cours Introduction à SQL Server, qui couvre spécifiquement SQL Server.


Allan Ouko's photo
Author
Allan Ouko
LinkedIn
Je crée des articles qui simplifient la science des données et l'analyse, en les rendant faciles à comprendre et accessibles.

Questions fréquemment posées

Qu'est-ce que SQL PIVOT ?

L'opérateur SQL PIVOT transforme les lignes en colonnes dans les résultats de la requête.

Quelles sont les bases de données qui supportent SQL PIVOT ?

SQL Server et Oracle prennent en charge l'opérateur PIVOT en mode natif. MySQL et PostgreSQL créent des tableaux croisés dynamiques à l'aide d'agrégations et d'instructions CASE.

Quelle est la différence entre PIVOT et UNPIVOT ?

L'opérateur PIVOT est utilisé pour transformer les lignes de données en colonnes en les agrégeant afin de les rendre lisibles. La clause UNPIVOT est utilisée pour transformer les colonnes en lignes.

Si vous utilisez PIVOT avec agrégation puis UNPIVOT, les données retrouvent-elles leur forme initiale ?

Non, l'utilisation de PIVOT avec une agrégation et l'application de UNPIVOT n'est généralement pas une opération exactement inverse.

Puis-je faire pivoter des données dynamiquement en SQL ?

SQL Server et PostgreSQL prennent en charge le pivotement dynamique. MySQL permet un pivotement dynamique à l'aide de procédures stockées.

PIVOT peut-il être combiné avec des clauses SQL ?

Vous pouvez combiner l'opérateur PIVOT avec des clauses SQL pour filtrer les données, notamment les clauses WHERE, GROUP BY et ORDER BY.

Les tableaux croisés sont-ils identiques aux tableaux croisés dynamiques en SQL ?

Oui, les tableaux croisés (cross-tabs) et les tableaux croisés dynamiques (pivot tables) dans SQL sont essentiellement le même concept. Les deux sont utilisés pour résumer et réorganiser les données afin de les rendre plus accessibles.

Sujets

Apprenez SQL avec DataCamp

cours

Introduction to SQL Server

4 hr
151.4K
Learn to use SQL Server to perform common data manipulation tasks and master common data manipulation tasks using this database system.
Afficher les détailsRight Arrow
Commencer le cours
Voir plusRight Arrow
Apparenté

blog

Les 20 meilleures questions d'entretien pour les flocons de neige, à tous les niveaux

Vous êtes actuellement à la recherche d'un emploi qui utilise Snowflake ? Préparez-vous à répondre à ces 20 questions d'entretien sur le flocon de neige pour décrocher le poste !
Nisha Arya Ahmed's photo

Nisha Arya Ahmed

20 min

blog

Q2 2023 DataCamp Donates Digest

DataCamp Donates a offert plus de 20k bourses d'études à nos partenaires à but non lucratif au deuxième trimestre 2023. Découvrez comment des apprenants défavorisés et assidus ont transformé ces opportunités en réussites professionnelles qui ont changé leur vie.
Nathaniel Taylor-Leach's photo

Nathaniel Taylor-Leach

blog

Les 32 meilleures questions d'entretien sur AWS et leurs réponses pour 2024

Un guide complet pour explorer les questions d'entretien AWS de base, intermédiaires et avancées, ainsi que des questions basées sur des situations réelles. Il couvre tous les domaines, garantissant ainsi une stratégie de préparation bien équilibrée.
Zoumana Keita 's photo

Zoumana Keita

30 min

blog

2022-2023 Rapport annuel DataCamp Classrooms

À l'aube de la nouvelle année scolaire, DataCamp Classrooms est plus motivé que jamais pour démocratiser l'apprentissage des données, avec plus de 7 650 nouveaux Classrooms ajoutés au cours des 12 derniers mois.
Nathaniel Taylor-Leach's photo

Nathaniel Taylor-Leach

8 min

blog

Nous avons fait don de bourses DataCamp Premium à un million de personnes, et ce n'est pas fini.

Réparties entre nos deux programmes d'impact social, DataCamp Classrooms et #DCDonates, les bourses offrent un accès illimité à tout ce que DataCamp Premium a à offrir.
Nathaniel Taylor-Leach's photo

Nathaniel Taylor-Leach

blog

Célébration de Saghar Hazinyar : Une boursière de DataCamp Donates et une diplômée de Code to Inspire

Découvrez le parcours inspirant de Saghar Hazinyar, diplômée de Code to Inspire, qui a surmonté les défis en Afghanistan et s'est épanouie grâce à une bourse de DataCamp Donates.
Fereshteh Forough's photo

Fereshteh Forough

4 min

Voir plusVoir plus