Réponse

Historiquement, les tâches de rapports dynamiques avec un nombre variable de regroupements n'étaient pas envisagées par la norme SQL — elle est conçue pour des requêtes statiquement décrites avec un schéma de résultat clairement défini. Avec l'avènement des plateformes BI et des tableaux de bord interactifs, les programmeurs ont commencé à chercher des moyens de construire des requêtes SQL "à la volée" afin de former des morceaux de données complexes sur demande de l'utilisateur.

Problème — SQL ne peut pas être programmé avec une logique if/else ordinaire à l'intérieur de SELECT — le nombre de colonnes, les champs eux-mêmes et même GROUP BY sont définis uniquement au moment de la compilation de la requête. Si l'utilisateur souhaite un découpage par plusieurs champs arbitraires, il est nécessaire de rassembler dynamiquement le texte de la requête et de l'exécuter via EXECUTE/Dynamic SQL.

Solution:

On génère le code SQL dans une application externe avec un appel ultérieur via EXEC/EXECUTE. Dans certains cas pour des tâches simples, on utilise CASE et des modèles rigides, mais pour plus de flexibilité, on recourt toujours au SQL dynamique :

Exemple de code (Pseudocode) :

-- Du côté de l'application (par exemple, Python)
groups = ['region', 'channel', 'month']
columns = [f'SUM({col}) AS {col}_sum' pour col dans selected_metrics]
group_by = ', '.join(groups)
selects = ', '.join(groups + columns)

query = f'SELECT {selects} FROM sales GROUP BY {group_by}'
-- Ensuite, envoi de cette requête via l'interface application/sql

Dans une SGBD prenant en charge EXECUTE :

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT ' + @selects + ' FROM sales GROUP BY ' + @group_by + ';';
EXEC sp_executesql @sql;

Caractéristiques clés :

Nécessite de vérifier les variables pour éviter les injections SQL
Est pratiquement toujours lié à l'architecture de l'application, des modèles pour SELECT/GROUP BY sont réalisés hors SQL
Avec un grand nombre d'options, un constructeur de requêtes intelligent ou un moteur de templates est nécessaire

Questions pièges.

Peut-on réaliser une modification dynamique du nombre de colonnes (pivot/unpivot) uniquement avec le SELECT standard sans SQL dynamique ?

Non — seules des valeurs préalablement connues peuvent être "déployées" via CASE/DECODE, mais un nombre inconnu de colonnes n'est atteint que dynamiquement.

Votre logique de SQL dynamique garantit-elle la protection contre les injections, si vous utilisez la concaténation de chaînes ?

Non, la concaténation manuelle de chaînes présente un risque constant d'injection SQL. Il est impératif de valider la liste des champs/groupe par whitelisting, d'éviter les fragments utilisateurs sans vérification, et encore mieux d'utiliser des paramètres là où c'est possible.

Le GROUP BY peut-il prendre une liste de colonnes via une variable ?

Le SQL standard ne prend pas en charge la transmission d'une liste de champs dans GROUP BY via une variable/paramètre. Il est nécessaire de former dynamiquement le texte de la requête — on ne peut pas simplement utiliser une variable dans la liste GROUP BY.

Erreurs typiques et anti-modèles

Noms de champs utilisateurs non vérifiés — injections
Liste de champs codée en dur — absence de flexibilité
Non-prise en compte des valeurs nulles possibles dans les groupes

Exemple de la vie réelle

Cas négatif

Un ingénieur BI a permis à l'utilisateur de transmettre des noms de champs pour le rapport directement depuis l'interface de l'application — sans les filtrer par whitelisting. En conséquence, lors du test d'injection, une table de production a été "plantée" en raison d'un code malveillant intégré dans le nom du champ.

Avantages :

Flexibilité maximale dans la construction des rapports

Inconvénients :

La sécurité souffre en raison de variables non préparées

Cas positif

L'ingénieur a mis en place une validation stricte des noms — l'utilisateur ne pouvait choisir que parmi des colonnes autorisées (issues de config/métadonnées), le SQL dynamique était assemblé uniquement par whitelisting, il n'y avait pas de voies d'injection.