Comment générer dynamiquement des colonnes dans SQL à l'aide de la fonction PIVOT ?

Génération dynamique de colonnes en SQL à l'aide de la fonction PIVOT

Cet article aborde le défi de la création dynamique de colonnes en SQL pour représenter différents types de récompenses dans une base de données de récompenses clients. Les types de récompenses ne sont pas fixes et peuvent changer au fil du temps. Nous explorerons des solutions utilisant la fonction SQL PIVOT, démontrant des approches statiques et dynamiques, et incluant l'ajout d'une colonne totale pour des résultats complets.

Le problème :

Nous avons des tables de base de données pour les clients, leurs récompenses et les types de récompenses. Les types de récompenses sont dynamiques ; ils peuvent être ajoutés ou supprimés. L'objectif est de générer un ensemble de résultats avec une colonne pour chaque type de récompense, indiquant le nombre de ce type de récompense pour chaque client.

Solution statique (nombre fixe de types de récompenses) :

Si les types de récompenses sont connus à l'avance, une simple requête PIVOT suffit :

SELECT name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne]
FROM
(
  SELECT c.name, cr.description, r.typeid
  FROM customers c
  LEFT JOIN rewards r ON c.id = r.customerid
  LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
) x
PIVOT
(
  COUNT(typeid)
  FOR description IN ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne])
) p;

Cette requête fonctionne bien, mais seulement si les types de récompense (« Bronze », « Argent », etc.) sont prédéfinis.

Solution dynamique (nombre inconnu de types de récompenses) :

Pour un nombre dynamique de types de récompenses, nous avons besoin de SQL dynamique :

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(description)
                    FROM customerrewards
                    GROUP BY description, typeid
                    ORDER BY typeid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'');

SET @query = 'SELECT name,' + @cols + ' FROM 
             (
                SELECT c.name, cr.description, r.typeid
                FROM customers c
                LEFT JOIN rewards r ON c.id = r.customerid
                LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
            ) x
            PIVOT 
            (
                COUNT(typeid)
                FOR description IN (' + @cols + ')
            ) p;';

EXECUTE(@query);

Ce code construit dynamiquement la requête SQL, en construisant la liste des colonnes à partir de la table customerrewards. Cela permet une adaptation à un certain nombre de types de récompenses.

Ajout d'une colonne Total :

Pour inclure le nombre total de récompenses par client, nous pouvons utiliser la fonction ROLLUP (les versions statiques et dynamiques sont présentées ci-dessous) :

Version statique avec total :

SELECT name, SUM([Bronze]) Bronze, SUM([Silver]) Silver, 
  SUM([Gold]) Gold, SUM([Platinum]) Platinum, SUM([AnotherOne]) AnotherOne
FROM 
(
  SELECT name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne]
  FROM
  (
    SELECT c.name, cr.description, r.typeid
    FROM customers c
    LEFT JOIN rewards r ON c.id = r.customerid
    LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
  ) x
  PIVOT
  (
    COUNT(typeid)
    FOR description IN ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne])
  ) p
) x
GROUP BY name WITH ROLLUP;

Version dynamique avec Total :

DECLARE @cols AS NVARCHAR(MAX),
    @colsRollup AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(description) 
                    FROM customerrewards
                    GROUP BY description, typeid
                    ORDER BY typeid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SELECT @colsRollup 
      = STUFF((SELECT ', SUM(' + QUOTENAME(description) + ') AS ' + QUOTENAME(description)
                    FROM customerrewards
                    GROUP BY description, typeid
                    ORDER BY typeid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');


SET @query 
          = 'SELECT name, ' + @colsRollup + '
             FROM
             (
                SELECT name,' + @cols + ' FROM 
                 (
                    SELECT c.name, cr.description, r.typeid
                    FROM customers c
                    LEFT JOIN rewards r ON c.id = r.customerid
                    LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
                ) x
                PIVOT 
                (
                    COUNT(typeid)
                    FOR description IN (' + @cols + ')
                ) p 
              ) x1
              GROUP BY name WITH ROLLUP;';

EXECUTE(@query);

Ces exemples SQL dynamiques fournissent une solution robuste et flexible pour générer des rapports avec un nombre variable de colonnes de type de récompense, y compris une colonne de total utile. N'oubliez pas d'adapter les noms des tables et des colonnes pour qu'ils correspondent à votre schéma de base de données spécifique.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!