Syntaxe PIVOT et des tableaux croisés selon le système

Vous connaissez déjà le pivot portable avec CASE. Mais les intervieweurs veulent aussi savoir si vous pouvez utiliser les opérateurs de pivot propres à chaque éditeur lorsqu'ils sont disponibles.

SQL Server fournit un opérateur PIVOT dédié. PostgreSQL propose une fonction crosstab dans l'extension tablefunc. Les connaître tous les deux, ainsi que leurs pièges, témoigne d'une expérience concrète.

Le PIVOT de SQL Server prend trois éléments :

• Un agrégat appliqué à la colonne de valeurs.
• Une clause FOR qui nomme la colonne dont les valeurs deviennent de nouvelles colonnes.
• Une liste IN des valeurs littérales à transformer en colonnes.

Il doit être appliqué à une table dérivée qui présente exactement la clé, la colonne de répartition et la valeur, et rien de plus.

Les intervieweurs testent un piège subtil de PIVOT : le regroupement est implicite. SQL Server regroupe selon chaque colonne de la source qui n'est ni la colonne agrégée ni la colonne FOR.

Si votre table dérivée contient par erreur une colonne supplémentaire comme order_id, le pivot l'utilise également pour le regroupement et vous obtenez bien plus de lignes que prévu. Réduisez toujours la requête interne à la clé, à la colonne de répartition et à la valeur.

Dans SQL Server, les noms des colonnes pivotées sont les valeurs littérales des données, entourées de crochets. Si une valeur commence par un chiffre ou contient des espaces, les crochets sont obligatoires.

Vous les sélectionnez en utilisant le même nom entre crochets dans le SELECT externe. C'est également pourquoi PIVOT ne peut pas gérer les valeurs inconnues sans SQL dynamique : la liste IN est codée en dur.

PostgreSQL ne possède pas de mot-clé PIVOT. À la place, l'extension tablefunc fournit crosstab, une fonction qui prend une chaîne SQL et restructure son résultat.

Vous devez d'abord activer l'extension. crosstab attend que la requête source renvoie exactement trois colonnes : l'identifiant de ligne, la catégorie et la valeur, dans cet ordre.

La partie la plus sujette aux erreurs de crosstab est la liste finale de définition des colonnes AS ct(...). Vous devez déclarer vous-même les noms et les types des colonnes de sortie, qui doivent correspondre au nombre et à l'ordre des catégories.

Si une catégorie est absente pour une ligne, crosstab la place selon sa position, ce qui peut décaler les données, sauf si vous utilisez la forme à deux arguments présentée ci-dessous.

Pour éviter les décalages lorsque certaines lignes ne possèdent pas certaines catégories, utilisez la forme à deux arguments. La seconde requête renvoie la liste complète et ordonnée des valeurs de catégorie, afin que crosstab sache exactement à quelle colonne chaque valeur appartient.

C'est la forme robuste attendue par les intervieweurs lorsque les catégories sont clairsemées.

Si l'intervieweur vous interroge sur MySQL, la réponse est directe : MySQL ne possède ni PIVOT ni crosstab. Votre seule possibilité est l'agrégation conditionnelle avec CASE — ou la forme abrégée SUM(... ) + IF().

C'est précisément pourquoi le modèle portable avec CASE est si apprécié : c'est le plus petit dénominateur commun qui fonctionne partout.

Une demande de rapport : « une ligne par région, avec une colonne comptant les commandes dans chaque état ». Dans SQL Server, alimentez PIVOT avec une table dérivée épurée en utilisant COUNT.

Comme vous comptez la colonne d'état elle-même, chaque ligne d'état non-NULL d'un groupe est comptabilisée. Le SELECT externe énumère chaque état sous forme de colonne entre crochets. C'est une alternative concise à l'écriture de trois expressions COUNT(CASE ...).

PIVOT et crosstab partagent la même limitation fondamentale que l'agrégation conditionnelle : les colonnes de sortie doivent être connues au moment où vous rédigez la requête.

• SQL Server : la liste IN est littérale.
• PostgreSQL avec crosstab : la liste de définition des colonnes est littérale.

Ni l'un ni l'autre ne peut découvrir les catégories au moment de l'exécution. Cela nécessite de construire dynamiquement la chaîne SQL.

Une bonne réponse d'entretien les compare honnêtement :

• Agrégation avec CASE : portable, lisible et fonctionnelle dans tous les moteurs. C'est le choix par défaut.
• SQL Server PIVOT : concise pour de nombreuses colonnes, mais son regroupement implicite surprend souvent.
• PostgreSQL crosstab : puissante, mais verbeuse, elle nécessite une extension et une liste de définition des colonnes.

En cas de doute, privilégiez l'agrégation conditionnelle et mentionnez les opérateurs propres aux éditeurs comme solutions alternatives.

Déterminez précisément le fonctionnement de SQL Server PIVOT sur lequel les intervieweurs vous interrogent.

La syntaxe de pivot propre aux éditeurs en un seul écran :

• SQL Server : PIVOT (SUM(x) FOR col IN ([a],[b])), avec un GROUP BY implicite sur les colonnes restantes.
• PostgreSQL : crosstab() de tablefunc, qui nécessite une liste de définition des colonnes ; utilisez la forme à deux arguments pour les données clairsemées.
• MySQL : aucun des deux n'existe, utilisez CASE.
• Dans les trois cas, les colonnes doivent être connues au moment de rédiger la requête.