Comment utiliser SQL PIVOT

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. 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. 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 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 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.