Leçon 12.9 · Temps de lecture : ~7 min

Un pivot transforme les valeurs d'une colonne en colonnes distinctes : au lieu de lignes « catégorie, classification, nombre », on obtient une ligne par catégorie avec une colonne pour chaque classification, comme un tableau croisé dynamique de tableur. Les pivots sont à la base de la plupart des rapports croisés, et « lignes en colonnes » est un exercice d'entretien fréquent. Dans cette leçon, vous ferez un pivot par agrégation conditionnelle, qui fonctionne dans toutes les bases, construirez un pivot dynamique dans MySQL, verrez l'opérateur PIVOT de SQL Server et repasserez des colonnes en lignes.

Pivot en SQL : des lignes aux colonnes

GROUP BY category, rating donne les bons chiffres sous une forme peu pratique : 80 lignes de paires catégorie–classification. Une matrice se lit plus facilement : les catégories en lignes, les classifications en colonnes. MySQL, MariaDB, PostgreSQL et SQLite n'ont pas de mot-clé PIVOT, et n'en ont pas besoin : chaque colonne de sortie est un agrégat avec une condition. C'est l'agrégation conditionnelle de la leçon 4.4, appliquée pour remodeler le résultat.

Les exemples s'exécutent sur la base d'exemple Sakila (MySQL 8.0).

Pivot par agrégation conditionnelle

Regroupez par l'étiquette des lignes (la catégorie) et, pour chaque future colonne, ne comptez que les lignes de cette classification :

SELECT
    c.name AS category,
    SUM(CASE WHEN f.rating = 'G' THEN 1 ELSE 0 END) AS g,
    SUM(CASE WHEN f.rating = 'PG' THEN 1 ELSE 0 END) AS pg,
    SUM(CASE WHEN f.rating = 'PG-13' THEN 1 ELSE 0 END) AS pg_13,
    SUM(CASE WHEN f.rating = 'R' THEN 1 ELSE 0 END) AS r,
    SUM(CASE WHEN f.rating = 'NC-17' THEN 1 ELSE 0 END) AS nc_17,
    COUNT(*) AS total
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c ON c.category_id = fc.category_id
GROUP BY c.name
ORDER BY c.name;

Résultat : 16 lignes, une par catégorie, avec le nombre de films de chaque classification et le total.

Comment cela fonctionne :

• GROUP BY c.name définit les lignes du pivot.
• Chaque CASE transforme un film en 1 s'il appartient à cette colonne et en 0 sinon : SUM compte donc les films d'une classification.
• Pour pivoter des montants plutôt que des nombres, placez la valeur dans le CASE : SUM(CASE WHEN ... THEN amount ELSE 0 END).

Dans MySQL, une comparaison renvoie 1 ou 0, donc SUM(f.rating = 'G') est un équivalent plus court. PostgreSQL propose COUNT(*) FILTER (WHERE f.rating = 'G').

Pivot par mois

Un rapport métier courant : le revenu par magasin, avec une colonne par mois. Les valeurs sont des montants et la condition porte sur le mois :

SELECT
    s.store_id,
    SUM(CASE WHEN MONTH(p.payment_date) = 5 THEN p.amount ELSE 0 END) AS may_2005,
    SUM(CASE WHEN MONTH(p.payment_date) = 6 THEN p.amount ELSE 0 END) AS jun_2005,
    SUM(CASE WHEN MONTH(p.payment_date) = 7 THEN p.amount ELSE 0 END) AS jul_2005,
    SUM(CASE WHEN MONTH(p.payment_date) = 8 THEN p.amount ELSE 0 END) AS aug_2005
FROM payment p
JOIN staff s ON s.staff_id = p.staff_id
WHERE p.payment_date >= '2005-01-01'
  AND p.payment_date < '2006-01-01'
GROUP BY s.store_id
ORDER BY s.store_id;

Résultat : 2 lignes ; par exemple, le magasin 1 a gagné 2621.83 en mai et 14003.54 en juillet 2005.

La condition WHERE limite les données à une année, car MONTH() seul mélangerait juillet 2005 et juillet 2006. Utilisez ELSE 0 quand une cellule vide doit afficher 0 ; sans ELSE, SUM renvoie NULL pour elle.

Pivot dynamique dans MySQL

Toutes les colonnes d'une requête doivent être connues avant son exécution. Si la liste des valeurs à pivoter change (nouvelles classifications, nouveaux mois), la requête doit être générée. Dans MySQL, construisez la liste des colonnes avec GROUP_CONCAT et exécutez le texte comme requête préparée :

SET @columns = (
    SELECT GROUP_CONCAT(DISTINCT
        CONCAT('SUM(f.rating = ''', rating, ''') AS `', rating, '`')
        ORDER BY rating)
    FROM film
);

SET @sql = CONCAT(
    'SELECT c.name AS category, ', @columns, '
     FROM film f
     JOIN film_category fc ON fc.film_id = f.film_id
     JOIN category c ON c.category_id = fc.category_id
     GROUP BY c.name
     ORDER BY c.name');

PREPARE pivot_stmt FROM @sql;
EXECUTE pivot_stmt;
DEALLOCATE PREPARE pivot_stmt;

Résultat : les mêmes 16 catégories avec une colonne par classification ; les colonnes sont construites à partir des données.

Deux mises en garde : les valeurs sont insérées dans le texte SQL, donc n'utilisez cela qu'avec des valeurs de vos propres tables, jamais avec une saisie utilisateur ; et de très longues listes de colonnes peuvent nécessiter un group_concat_max_len plus grand (1024 caractères par défaut). Souvent, il est plus simple de renvoyer le long résultat du GROUP BY et de le pivoter dans l'application ou l'outil de BI.

L'opérateur PIVOT dans SQL Server et Oracle

SQL Server et Oracle ont une syntaxe dédiée. Le même rapport dans SQL Server :

SELECT category, [G], [PG], [PG-13], [R], [NC-17]
FROM (
    SELECT c.name AS category, f.rating, f.film_id
    FROM film f
    JOIN film_category fc ON fc.film_id = f.film_id
    JOIN category c ON c.category_id = fc.category_id
) AS src
PIVOT (
    COUNT(film_id) FOR rating IN ([G], [PG], [PG-13], [R], [NC-17])
) AS pvt;

Les valeurs de IN (...) doivent quand même être listées d'avance. L'agrégation conditionnelle fait la même chose et fonctionne partout : c'est la réponse la plus sûre en entretien.

Unpivot : des colonnes de nouveau en lignes

La tâche inverse se présente quand les données arrivent sous forme de feuille de calcul : une colonne par mois. Dépivotez-les avec un SELECT par colonne, réunis par UNION ALL :

WITH store_months AS (
    SELECT
        s.store_id,
        SUM(CASE WHEN MONTH(p.payment_date) = 7 THEN p.amount ELSE 0 END) AS jul_2005,
        SUM(CASE WHEN MONTH(p.payment_date) = 8 THEN p.amount ELSE 0 END) AS aug_2005
    FROM payment p
    JOIN staff s ON s.staff_id = p.staff_id
    WHERE p.payment_date >= '2005-07-01'
      AND p.payment_date < '2005-09-01'
    GROUP BY s.store_id
)
SELECT store_id, '2005-07' AS month, jul_2005 AS amount FROM store_months
UNION ALL
SELECT store_id, '2005-08', aug_2005 FROM store_months
ORDER BY store_id, month;

Résultat : 4 lignes, une par magasin et par mois, par exemple magasin 1 – 2005-07 – 14003.54.

SQL Server et Oracle ont aussi un opérateur UNPIVOT ; PostgreSQL peut utiliser CROSS JOIN LATERAL (VALUES ...).

Pratique

• Répartition de la population (pivot)
• Répartition des films par catégorie et magasin
• Revenu par mois et type de paiement

Points clés :

• Un pivot transforme les valeurs d'une colonne en colonnes distinctes.
• GROUP BY définit les lignes ; SUM(CASE WHEN column = value THEN ... ELSE 0 END) crée chaque colonne. Cela fonctionne dans toutes les bases.
• Pour des montants, placez la valeur dans THEN ; pour des nombres, placez 1.
• Quand les colonnes ne sont pas connues d'avance, générez la requête : dans MySQL avec GROUP_CONCAT et PREPARE.
• SQL Server et Oracle ont PIVOT et UNPIVOT ; UNION ALL dépivote partout.

Questions fréquentes

Comment transformer des lignes en colonnes dans MySQL ?

MySQL n'a pas d'opérateur PIVOT. Utilisez l'agrégation conditionnelle : un GROUP BY sur l'étiquette des lignes et un SUM(CASE WHEN pivot_column = 'value' THEN amount ELSE 0 END) AS value par colonne de sortie.

Comment créer un pivot avec des colonnes dynamiques ?

Générez le texte SQL : rassemblez les valeurs distinctes avec GROUP_CONCAT, construisez à partir d'elles la liste des SUM(CASE ...), puis exécutez la requête avec PREPARE et EXECUTE. Dans SQL Server, on fait de même avec STRING_AGG et sp_executesql. N'utilisez que des valeurs fiables issues de vos propres tables.

Quelle est la différence entre PIVOT et l'agrégation conditionnelle ?

Elles donnent le même résultat. PIVOT est une syntaxe plus courte, disponible seulement dans SQL Server et Oracle. L'agrégation conditionnelle fonctionne dans toutes les bases et permet des conditions et des agrégats différents pour chaque colonne.

Comment repasser des colonnes en lignes (unpivot) ?

Écrivez un SELECT par colonne, chacun renvoyant la clé, le nom de la colonne sous forme de littéral et la valeur de la colonne, puis réunissez-les avec UNION ALL. SQL Server et Oracle ont aussi UNPIVOT.

→ D'autres exercices sur la base de données Sakila