Wie generiert man dynamisch Spalten in SQL, um Kundenprämien zu zählen?

Spalten dynamisch in SQL erstellen

Dynamische Spalten

Die Aufgabe umfasst das Generieren dynamischer Spalten in SQL, um die Prämienanzahl für jeden Kundentyp anzuzeigen.

Tabellenstruktur und Daten

Wir haben folgende Formulare:

• Kunde (Kundennummer, Name)
• Kundenprämien (Typ-ID, Beschreibung)
• Prämie (Prämien-ID, Typ-ID, Kunden-ID)

Nachfrage

Das Ziel besteht darin, Spalten für jeden Prämientyp zu erstellen und die Prämienanzahl pro Kunde pro Spalte zusammen mit einer Gesamtzeile anzuzeigen.

Lösung

1. Verwenden Sie PIVOT mit bekannter Spaltenanzahl

Für eine feste Anzahl von Spalten können Sie die PIVOT-Funktion verwenden:

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;

2. PIVOT mit dynamischem SQL

Für eine unbekannte Anzahl von Spalten verwenden Sie dynamisches 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)

Gesamtzeile

Um eine Gesamtzeile einzuschließen, verwenden Sie ROLLUP:

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

Fazit

Mit der oben genannten Lösung können Sie dynamisch Spalten basierend auf den verfügbaren Typen generieren und die Prämienanzahl pro Spalte für jeden Kunden anzeigen, einschließlich einer Gesamtzeile.

Diese Antwort behält das Bild und sein ursprüngliches Format bei, formuliert den Text neu, um eine nahezu synonyme Ersetzung zu erreichen und gleichzeitig die ursprüngliche Bedeutung beizubehalten, und vermeidet größere strukturelle Änderungen.

Das obige ist der detaillierte Inhalt vonWie generiert man dynamisch Spalten in SQL, um Kundenprämien zu zählen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!