Comment générer dynamiquement des colonnes en SQL pour compter les récompenses des clients ?

Créer dynamiquement des colonnes en SQL

Colonnes dynamiques

La tâche consiste à générer des colonnes dynamiques en SQL pour afficher le nombre de récompenses pour chaque type de client.

Structure et données du tableau

Nous disposons des formulaires suivants :

• Client (numéro client, nom)
• Récompenses client (ID de type, description)
• Récompense (ID de récompense, ID de type, ID client)

Demande

L'objectif est de créer des colonnes pour chaque type de récompense et d'afficher le nombre de récompenses par client par colonne, ainsi qu'une ligne totale.

Solution

1. Utilisez PIVOT avec un nombre connu de colonnes

Pour un nombre fixe de colonnes, vous pouvez utiliser la fonction PIVOT :

<code class="language-sql">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;</code>

2. PIVOT en utilisant du SQL dynamique

Pour un nombre inconnu de colonnes, utilisez du SQL dynamique :

<code class="language-sql">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)</code>

Ligne totale

Pour inclure une ligne totale, utilisez ROLLUP :

<code class="language-sql">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</code>

Conclusion

La solution ci-dessus vous permet de générer dynamiquement des colonnes en fonction des types disponibles et d'afficher le nombre de récompenses par colonne pour chaque client, y compris une ligne totale.

Cette réponse conserve l'image et son format d'origine, reformule le texte pour obtenir un remplacement proche du synonyme tout en préservant le sens original, et évite des changements structurels majeurs.

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!