Wie generiert man dynamisch Spalten in SQL mithilfe der PIVOT-Funktion?

Dynamisches Generieren von Spalten in SQL mithilfe der PIVOT-Funktion

Dieser Artikel befasst sich mit der Herausforderung, Spalten in SQL dynamisch zu erstellen, um unterschiedliche Prämientypen in einer Kundenprämiendatenbank darzustellen. Die Belohnungsarten sind nicht festgelegt und können sich im Laufe der Zeit ändern. Wir untersuchen Lösungen mithilfe der SQL-PIVOT-Funktion, demonstrieren sowohl statische als auch dynamische Ansätze und schließen das Hinzufügen einer Gesamtspalte für umfassende Ergebnisse ein.

Das Problem:

Wir haben Datenbanktabellen für Kunden, ihre Prämien und die Arten von Prämien. Die Arten der Belohnungen sind dynamisch; Sie können hinzugefügt oder entfernt werden. Das Ziel besteht darin, einen Ergebnissatz mit einer Spalte für jeden Prämientyp zu generieren, der die Anzahl dieses Prämientyps für jeden Kunden anzeigt.

Statische Lösung (feste Anzahl von Belohnungstypen):

Wenn die Belohnungsarten vorher bekannt sind, reicht eine einfache PIVOT-Abfrage:

<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>

Diese Abfrage funktioniert gut, aber nur, wenn die Belohnungstypen („Bronze“, „Silber“ usw.) vordefiniert sind.

Dynamische Lösung (unbekannte Anzahl von Belohnungstypen):

Für eine dynamische Anzahl von Belohnungstypen benötigen wir dynamisches SQL:

<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>

Dieser Code erstellt die SQL-Abfrage dynamisch und erstellt die Spaltenliste aus der Tabelle customerrewards. Dies ermöglicht eine Anpassung an beliebig viele Belohnungsarten.

Hinzufügen einer Gesamtspalte:

Um die Gesamtzahl der Prämien pro Kunde einzubeziehen, können wir die Funktion ROLLUP verwenden (sowohl statische als auch dynamische Versionen werden unten angezeigt):

Statische Version mit Gesamtsumme:

<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>

Dynamische Version mit Gesamtsumme:

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

Diese dynamischen SQL-Beispiele bieten eine robuste und flexible Lösung zum Erstellen von Berichten mit einer variablen Anzahl von Belohnungstypspalten, einschließlich einer hilfreichen Gesamtspalte. Denken Sie daran, Tabellen- und Spaltennamen an Ihr spezifisches Datenbankschema anzupassen.

Das obige ist der detaillierte Inhalt vonWie generiert man dynamisch Spalten in SQL mithilfe der PIVOT-Funktion?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!