Tableaux croisés dynamiques avec des colonnes inconnues

Tout pivotage statique, qu'il repose sur une agrégation avec CASE, sur PIVOT du serveur SQL ou sur crosstab de PostgreSQL, présente la même limitation : vous devez énumérer les colonnes de sortie lorsque vous écrivez la requête.

Mais que faire si les catégories sont inconnues, par exemple des noms de produits qui changent chaque semaine ou une colonne par mois actif ? Il s'agit d'un pivotage dynamique, une question d'entretien destinée aux candidats expérimentés, car le SQL ordinaire ne peut pas renvoyer un résultat dont la liste de colonnes est déterminée à l'exécution.

Le SQL est à typage statique au niveau de l'ensemble de résultats : le planificateur doit connaître les colonnes et leurs types avant l'exécution. Une seule requête ne peut pas dire créez une colonne pour chaque valeur que vous trouverez.

La technique universelle consiste donc à générer le texte SQL en deux étapes : interroger d'abord les catégories distinctes, puis construire à partir d'elles une chaîne de requête de pivotage et exécuter cette chaîne.

La première étape consiste à exécuter une requête ordinaire qui répertorie les valeurs distinctes destinées à devenir des colonnes. Vous les classez généralement afin d'obtenir une disposition stable des colonnes.

Ce résultat alimente l'étape de construction de la chaîne. Dans un système réel, vous exécutez cette requête, récupérez les lignes et composez la requête suivante à partir de celles-ci.

Ensuite, transformez ces valeurs en une liste séparée par des virgules d'expressions CASE (ou en noms entre crochets pour PIVOT). Les bases de données fournissent des fonctions d'agrégation de chaînes qui permettent d'effectuer cette opération directement en SQL.

Dans PostgreSQL, il s'agit de string_agg ; dans MySQL, de GROUP_CONCAT ; dans le serveur SQL, de STRING_AGG ou de l'ancienne astuce FOR XML PATH.

Concaténez le fragment généré pour former une chaîne de requête complète, puis exécutez-la dynamiquement : EXECUTE dans PL/pgSQL, sp_executesql dans le serveur SQL, ou PREPARE/EXECUTE dans MySQL.

C'est le cœur d'un pivotage dynamique : le SQL écrit du SQL, puis l'exécute.

Dans PostgreSQL, regroupez les trois étapes dans un bloc DO ou une fonction. Construisez la liste des colonnes avec string_agg, insérez-la dans la requête, puis exécutez celle-ci avec EXECUTE.

Comme les colonnes du résultat sont inconnues jusqu'à l'exécution, une fonction qui renvoie ce résultat utilise souvent RETURNS SETOF record ou renvoie les lignes sous forme de json, que l'appelant développe ensuite.

MySQL ne possède pas d'opérateur de pivotage ; les pivotages dynamiques construisent donc une chaîne d'agrégation conditionnelle avec GROUP_CONCAT, puis l'exécutent au moyen d'une instruction préparée.

GROUP_CONCAT est soumis à une limite de longueur (group_concat_max_len) que les personnes menant l'entretien peuvent mentionner ; augmentez-la si vous avez de nombreuses catégories.

Comme vous concaténez des valeurs de données dans du SQL exécutable, les pivotages dynamiques comportent un risque d'injection. Si une valeur de catégorie contient une apostrophe ou un texte malveillant, elle peut interrompre la requête générée ou en prendre le contrôle.

Échappez toujours les identifiants et les littéraux avec les fonctions sécurisées du moteur : format('%I', ...) et %L dans PostgreSQL, QUOTENAME dans le serveur SQL. Ne placez jamais directement des valeurs brutes dans la chaîne.

Deuxième difficulté : l'appelant ne peut pas connaître à l'avance la structure du résultat. Voici des stratégies couramment acceptées lors d'un entretien :

• Renvoyer les lignes sous forme de JSON et laisser la couche applicative développer les clés.
• Faire afficher ou construire la requête par la procédure, puis l'exécuter lors d'une deuxième étape.
• Effectuer le pivotage final dans le code applicatif (bibliothèque pandas, outil de BI) une fois les catégories connues.

Il n'existe pas de moyen simple de renvoyer des colonnes arbitraires au moyen d'un seul appel statique.

Supposons que des produits apparaissent et disparaissent, et que le rapport ait besoin d'une colonne de chiffre d'affaires par produit actuellement présent dans sales. Vous ne pouvez pas inscrire la liste en dur ; vous devez donc la générer. PostgreSQL rend cette approche lisible : construisez le fragment CASE avec string_agg et un échappement sûr, insérez-le dans une requête, puis exécutez celle-ci avec EXECUTE.

Présentez le raisonnement à la personne qui mène l'entretien : découvrez les produits, formatez chacun en colonne entre guillemets, assemblez le tout, puis exécutez-le. La même démarche s'applique à tous les moteurs ; seules les fonctions utilitaires changent.

Les candidats expérimentés savent quand ne pas effectuer cette opération en SQL. Le SQL dynamique est plus difficile à lire, à tester, à sécuriser et à mettre en cache. Souvent, la meilleure réponse consiste à :

• Renvoyer les données en forme longue depuis le SQL, puis effectuer le pivotage dans l'application ou la couche de production de rapports.
• Si l'ensemble des catégories est réduit et évolue lentement, utiliser un pivotage statique et le mettre à jour de temps en temps.

Réservez les pivotages dynamiques aux ensembles de catégories réellement ouverts et en évolution constante.

Vérifiez la raison fondamentale de l'existence des pivotages dynamiques.

Les pivotages dynamiques gèrent les ensembles de colonnes inconnus :

• Les pivotages statiques échouent, car les colonnes du résultat doivent être fixées avant l'exécution.
• Schéma : interroger les catégories distinctes, construire une chaîne SQL de pivotage, puis l'exécuter dynamiquement.
• Utilisez string_agg/GROUP_CONCAT/STRING_AGG pour construire la liste des colonnes.
• Échappez les valeurs (%I/%L, QUOTENAME) afin d'éviter les injections SQL.
• Il est souvent plus simple de renvoyer les données en forme longue et d'effectuer le pivotage dans la couche applicative.