PostgreSQL crosstab : la fonction tablefunc

PostgreSQL propose la fonction crosstab() via l'extension tablefunc. Elle est plus performante sur de gros volumes mais nécessite une configuration préalable :

-- Activer l'extension (une seule fois par base)
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- Utilisation
SELECT *
FROM crosstab(
    'SELECT region, mois, SUM(revenu)
     FROM ventes
     GROUP BY region, mois
     ORDER BY 1, 2',
    'VALUES (''2025-01''), (''2025-02'')'
) AS ct(region TEXT, "jan_2025" NUMERIC, "fev_2025" NUMERIC);

La fonction crosstab prend 2 requêtes en argument : la première renvoie les données source triées, la seconde liste les valeurs de pivot attendues. Le typage explicite des colonnes de sortie est obligatoire.

Pivot dynamique : quand les colonnes ne sont pas connues à l'avance

Le défi du pivot dynamique survient lorsque les valeurs à pivoter (les mois, les catégories, les statuts) varient et ne sont pas connues au moment de l'écriture de la requête.

Approche en SQL Server avec SQL dynamique

DECLARE @colonnes NVARCHAR(MAX);
DECLARE @sql      NVARCHAR(MAX);

-- Récupérer dynamiquement la liste des mois
SELECT @colonnes = STRING_AGG(QUOTENAME(mois), ', ')
FROM (SELECT DISTINCT mois FROM ventes) AS mois_distincts;

-- Construire et exécuter la requête PIVOT
SET @sql = N'
    SELECT region, ' + @colonnes + '
    FROM (SELECT region, mois, revenu FROM ventes) src
    PIVOT (SUM(revenu) FOR mois IN (' + @colonnes + ')) pvt
    ORDER BY region;';

EXEC sp_executesql @sql;

Cette technique est puissante mais comporte des risques : injection SQL si les données proviennent d'une source non maîtrisée, et difficulté à typer les colonnes de sortie statiquement. Dans la majorité des projets data réels, le pivot dynamique est délégué à la couche applicatrice (Python pandas, dbt, etc.).

Unpivot : l'opération inverse

L'opération inverse du pivot s'appelle unpivot : elle transforme des colonnes en lignes. C'est utile pour normaliser une table "wide" reçue d'une source externe.

Unpivot avec CROSS JOIN LATERAL (PostgreSQL)

SELECT region, mois, revenu
FROM ventes_wide
CROSS JOIN LATERAL (
    VALUES ('2025-01', "2025-01"),
           ('2025-02', "2025-02")
) AS t(mois, revenu)
ORDER BY region, mois;

SQL Server dispose d'un opérateur UNPIVOT natif avec une syntaxe symétrique à PIVOT. PostgreSQL favorise CROSS JOIN LATERAL ou unnest.

Pour aller plus loin sur les transformations de données, consultez notre guide sur les opérateurs ensemblistes SQL : UNION, INTERSECT, EXCEPT qui permettent de combiner des résultats pivotés issus de plusieurs sources.

Pivot SQL en entretien technique : ce que les recruteurs évaluent

Le pivot SQL est un sujet récurrent dans les entretiens data analyst et data engineer. Voici ce qui est généralement évalué :

• La maîtrise de la méthode universelle (CASE WHEN + GROUP BY) — attendue de tout candidat
• La connaissance des extensions natives du moteur cible (SQL Server PIVOT, PostgreSQL crosstab)
• La gestion des NULLs — savoir quand utiliser COALESCE(valeur, 0) pour éviter les NULLs dans les colonnes pivotées
• La performance — comprendre pourquoi un pivot sur 100 millions de lignes nécessite une réflexion sur les index et la cardinalité
• L'approche dynamique — expliquer pourquoi on l'évite en SQL pur et comment on le délègue

Les questions d'entretien sur le pivot s'articulent souvent avec les fonctions fenêtre SQL, car les deux compétences sont fréquemment combinées dans les exercices de reporting avancé.

Gestion des NULLs et valeurs manquantes dans un pivot

Un pivot produit naturellement des NULL pour les combinaisons qui n'existent pas dans les données source. Par exemple, si la région "Est" n'a aucune vente en février, la colonne 2025-02 sera NULL pour cette ligne.

Pour traiter ces cas, enveloppez vos expressions dans COALESCE :

SELECT
    region,
    COALESCE(SUM(CASE WHEN mois = '2025-01' THEN revenu END), 0) AS "2025-01",
    COALESCE(SUM(CASE WHEN mois = '2025-02' THEN revenu END), 0) AS "2025-02"
FROM ventes
GROUP BY region;

La différence entre ELSE 0 et COALESCE(..., 0) est subtile mais importante :

• CASE WHEN ... ELSE 0 END renvoie 0 dès que la condition n'est pas remplie — même si la ligne existe
• CASE WHEN ... THEN valeur END (sans ELSE) renvoie NULL si la condition est fausse, et COALESCE le remplace ensuite par 0

Les 2 approches donnent le même résultat dans la majorité des cas, mais la seconde est plus explicite sur l'intention sémantique : "il n'y a pas de données" versus "la valeur est zéro".

Optimiser les performances d'un pivot sur grand volume

Sur des tables de plusieurs dizaines de millions de lignes, un pivot mal écrit peut être catastrophiquement lent. Voici les leviers d'optimisation :

• Filtrer avant de pivoter : appliquez les clauses WHERE dans une CTE ou sous-requête avant la rotation, pas après
• Indexer la colonne pivotée : créez un index sur la colonne dont les valeurs deviennent des en-têtes (ici mois)
• Matérialiser les agrégats intermédiaires : si le pivot est exécuté fréquemment, pré-agréger les données dans une vue matérialisée ou une table de staging
• Limiter le nombre de colonnes : au-delà de 20-30 colonnes pivotées, envisagez une représentation alternative (JSON, tableau croisé côté application)

Pour un traitement complet de l'optimisation, référez-vous à notre article sur les techniques d'optimisation des requêtes SQL.

Questions fréquentes

Quelle est la différence entre PIVOT et CASE WHEN pour pivoter des données ?

CASE WHEN est une approche universelle qui fonctionne sur tous les moteurs SQL. L'opérateur PIVOT (SQL Server, Oracle, BigQuery, DuckDB) est une syntaxe native plus concise, mais qui produit le même résultat. En entretien, maîtriser CASE WHEN est indispensable ; connaître PIVOT est un plus apprécié.

Comment pivoter des données quand les colonnes ne sont pas connues à l'avance ?

C'est le cas du pivot dynamique. En SQL pur, il faut construire la requête en chaîne de caractères et l'exécuter via EXEC (SQL Server) ou EXECUTE (PostgreSQL). En pratique, cette logique est souvent déplacée côté application (Python, dbt) pour plus de lisibilité et de sécurité.

Le pivot SQL est-il différent d'un tableau croisé dynamique Excel ?

Fonctionnellement, oui c'est équivalent — les 2 transforment des lignes en colonnes agrégées. La différence est que le pivot SQL est déclaratif et reproductible : la requête peut être versionnée, testée et exécutée à grande échelle. Un tableau croisé Excel dépend d'une interface graphique et ne se prête pas à l'automatisation.

Peut-on pivoter sur plusieurs colonnes simultanément ?

Oui, mais cela complexifie considérablement la requête. Il faut concaténer les valeurs des 2 colonnes pour créer des identifiants uniques, puis pivoter sur ces identifiants composites. Par exemple, region || '_' || mois comme valeur à pivoter. Au-delà de 2 dimensions, envisagez un modèle OLAP ou un outil de BI dédié.

Comment tester qu'un pivot est correct ?

La meilleure vérification est de comparer la somme totale avant et après le pivot. Avant : SELECT SUM(revenu) FROM ventes doit être égale à la somme de toutes les cellules du tableau pivoté. Un écart signale un doublon, un NULL non géré ou une erreur de condition dans le CASE WHEN.

Le pivot affecte-t-il les performances de façon significative ?

Oui, un pivot implique un GROUP BY complet sur la table source. Sur des volumes importants (10M+ lignes), assurez-vous que la colonne pivotée est indexée et que les filtres WHERE sont appliqués en amont. Une vue matérialisée peut réduire drastiquement le temps de réponse pour les rapports récurrents.

BigQuery supporte-t-il l'opérateur PIVOT natif ?

Oui, depuis 2021, BigQuery supporte la syntaxe PIVOT avec une syntaxe similaire à SQL Server. DuckDB — moteur analytique en plein essor — va encore plus loin avec un PIVOT automatique qui déduit les colonnes sans les lister explicitement, ce qui est idéal pour l'exploration rapide de données.

Conclusion

Le pivot SQL est une compétence fondamentale pour tout data analyst ou data engineer qui produit des rapports ou prépare des données pour la visualisation. La maîtrise de la méthode CASE WHEN + GROUP BY vous garantit une solution portable sur tous les moteurs ; la connaissance des opérateurs natifs (PIVOT, crosstab, FILTER) vous permet d'écrire du code plus expressif sur les plateformes qui les supportent.

Retenez les 3 points essentiels : filtrez vos données avant de pivoter, gérez les NULL avec COALESCE, et déléguez le pivot dynamique à la couche applicative. Avec ces principes, vous répondrez à n'importe quelle question de pivot en entretien avec clarté et précision.

Envie de pratiquer ? Retrouvez des exercices SQL interactifs sur le pivot, les fonctions fenêtre et bien d'autres sujets directement sur SQL Pratique.