Zusammenfassung: In diesem Lernprogramm erfahren Sie, wie Sie den SQL Server-Operator PIVOT verwenden zum Konvertieren von Zeilen in Spalten.
Einrichten der Ziele
Für die Demonstration verwenden wir production.products und production.categories Tabellen aus der Beispieldatenbank:
Die folgende Abfrage ermittelt die Anzahl der Produkte für jede Produktkategorie:
Hier ist die Ausgabe:
Unser Ziel ist es, die Kategorienamen aus der ersten Spalte der Ausgabe in mehrere Spalten umzuwandeln und die Anzahl der Produkte für jede zu zählen Kategoriename wie folgt:
Zusätzlich können wir das Modelljahr hinzufügen, um die Kategorie zu gruppieren oder nach Modelljahr, wie in der folgenden Ausgabe gezeigt:
Einführung in den SQL Server PIVOT-Operator
Der SQL Server PIVOT -Operator dreht einen Ausdruck mit Tabellenwert. Die eindeutigen Werte in einer Spalte werden in der Ausgabe in mehrere Spalten umgewandelt und es werden Aggregationen für alle verbleibenden Spaltenwerte durchgeführt.
Führen Sie die folgenden Schritte aus, um eine Abfrage zu einer Pivot-Tabelle zu machen:
- Wählen Sie zunächst ein Basisdatensatz zum Schwenken aus.
- Zweitens erstellen Sie ein temporäres Ergebnis mithilfe einer abgeleiteten Tabelle oder eines allgemeinen Tabellenausdrucks (CTE).
- Wenden Sie drittens die
PIVOT-Operator.
Wenden wir diese Schritte im folgenden Beispiel an.
Wählen Sie zunächst den Kategorienamen und die Produkt-ID aus dem production.products und production.categories Tabellen als Basisdaten für das Schwenken:
Erstellen Sie zweitens eine temporäre Ergebnismenge mithilfe einer abgeleiteten Tabelle:
Drittens wenden Sie den Operator PIVOT an:
Diese Abfrage generiert die folgende Ausgabe:
Nun jede zusätzliche Spalte, die Sie der Auswahlliste der Abfrage hinzufügen, die die zurückgibt Base Daten bilden automatisch Zeilengruppen in der Pivot-Tabelle. Beispielsweise können Sie der obigen Abfrage die Spalte Modelljahr hinzufügen:
Hier ist die Ausgabe:
Spaltenwerte generieren
In der obigen Abfrage mussten Sie jeden Kategorienamen in Klammern nach der IN Operator manuell. Um dies zu vermeiden, können Sie die Funktion QUOTENAME() verwenden, um die Kategorienamensliste zu generieren und über die Abfrage zu kopieren.
Generieren Sie zunächst die Kategorienamensliste:
Die Ausgabe sieht folgendermaßen aus:
In diesem Snippet:
- Die Funktion
QUOTENAME()umschließt die Kategoriename in eckigen Klammern, z. B. - Die Funktion
LEFT()entfernt das letzte Komma aus der Zeichenfolge @columns
Kopieren Sie anschließend die Kategorienamensliste aus der Ausgabe und fügen Sie sie in die Abfrage ein.
Dynamische Pivot-Tabellen
Wenn Sie eine hinzufügen Wenn Sie einen neuen Kategorienamen in die Tabelle production.categories eingeben, müssen Sie Ihre Abfrage neu schreiben, was nicht ideal ist. Um dies zu vermeiden, können Sie dynamisches SQL verwenden, um die Pivot-Tabelle dynamisch zu gestalten.
In dieser Abfrage wird keine feste Liste von Kategorienamen an PIVOT Operator erstellen wir die Kategorienamensliste und übergeben sie an eine SQL-Anweisung. Anschließend führen wir diese Anweisung dynamisch mit der gespeicherten Prozedur sp_executesql aus.
In diesem Lernprogramm Sie haben gelernt, wie Sie mit der SQL Server-Tabelle PIVOT Zeilen in Spalten konvertieren.