Dieser Artikel gibt einen vollständigen Überblick über die Verwendung der PIVOT- und UNPIVOT-Operatoren in SQL Server. Die PIVOT- und UNPIVOT-Operatoren ähneln den relationalen Operatoren, die Folgendes ermöglichen Umwandeln des Tabellenwertausdrucks in eine andere Tabelle . Beide Betreiber erstellen mehrdimensionale Berichte, die dabei helfen, große Datenmengen schnell zu kombinieren und zu vergleichen.
Wir können das nutzen PIVOT-Operator wenn wir Tabellenwertausdrücke transformieren müssen. Es spaltet die eindeutige Werte aus einer Spalte in viele Spalten im Endergebnis. Es auch Aggregate die verbleibenden Spaltenwerte, die im Endergebnis benötigt werden. UNPIVOT-Operator Konvertiert Daten aus Spalten eines Tabellenwertausdrucks in Spaltenwerte, was die Umkehrung von PIVOT ist.
Lassen Sie es uns anhand des folgenden einfachen Diagramms verstehen:
Auf der linken Seite dieser Abbildung sehen wir die Originaldatensatz , das drei Spalten hat: Jahr, Region, Und Verkäufe . Als nächstes sehen wir auf der rechten Seite die PIVOT-Tabelle, die durch Drehen des erstellt wird Region (Zeilen) in Nord und Süd (Spalten) . Nachdem wir Zeilen in Spalten umgewandelt haben, können wir Folgendes tun: Aggregat der Sales-Spaltenwerte für jeden Schnittpunkt zwischen den Spalten und Zeilen der PIVOT-Tabelle.
Lassen Sie uns zunächst eine Tabelle mit dem Namen erstellen Pivot_demo um die PIVOT- und UNPIVOT-Operatoren zu demonstrieren. Die folgende Anweisung erstellt eine neue Tabelle in unserer angegebenen Datenbank:
CREATE TABLE pivot_demo ( Region varchar(45), Year int, Sales int )
Fügen Sie als Nächstes einige Daten wie folgt in diese Tabelle ein:
INSERT INTO pivot_demo VALUES ('North', 2010, 72500), ('South', 2010, 60500), ('South', 2010, 52000), ('North', 2011, 45000), ('South', 2011, 82500), ('North', 2011, 35600), ('South', 2012, 32500), ('North', 2010, 20500);
Wir können die Daten mit der SELECT-Anweisung überprüfen. Wir erhalten die folgende Ausgabe:
PIVOT-Operator
Dieser Operator wird zum Drehen von Tabellenwertausdrücken verwendet. Es wurde erstmals in der SQL Server 2005-Version eingeführt. Es wandelt Daten von Zeilen in Spalten um. Es teilt die eindeutigen Werte einer Spalte in viele Spalten auf und aggregiert dann die verbleibenden Spaltenwerte, die im Endergebnis erforderlich sind.
Wir müssen die folgenden Schritte ausführen, um eine PIVOT-Tabelle zu erstellen:
- Wählen Sie den Basisdatensatz für die Pivotierung aus.
- Erstellen Sie temporäre Ergebnisse mithilfe einer abgeleiteten Tabelle oder eines CTE (Common Table Expression).
- Nutzen Sie den PIVOT-Operator.
Syntax
Die folgende Syntax veranschaulicht die Verwendung von PIVOT in SQL Server:
SELECT , FROM () AS PIVOT ( () FOR [] IN ( [list of pivoted columns]) ) AS <alias name for pivot table> </alias>
Wenn wir dieses Skript unterbrechen, können wir sehen, dass es zwei separate Abschnitte hat. Der erste Abschnitt wählt Daten aus der Haupttabelle aus und der zweite Abschnitt bestimmt, wie die PIVOT-Tabelle erstellt wird. Der zweite Teil enthält auch einige spezielle Schlüsselwörter wie SUM, FOR und IN. Sehen wir uns die Bedeutung dieser Schlüsselwörter im PIVOT-Operator an.
SUMME
Dieser Operator ist gewohnt Aggregieren Sie die Werte aus der angegebenen Spalte, die in der PIVOT-Tabelle verwendet werden soll. Wir müssen es mit dem PIVOT-Operator verwenden, um die aggregierten Spaltenanzeigen für die Werteabschnitte zu erhalten.
FOR-Schlüsselwort
Dieses Schlüsselwort wird für die PIVOT-Tabellenanweisung verwendet Weisen Sie den PIVOT-Operator an auf welche Spalte die PIVOT-Funktion angewendet werden soll. Im Grunde gibt es die Spaltennamen an, die von Zeilen in Spalten umgewandelt werden.
IN-Schlüsselwort
Dieses Schlüsselwort listet alle eindeutigen Werte auf aus der PIVOT-Spalte, die als Spalten der PIVOT-Tabelle angezeigt werden sollen.
Beispiel
Lassen Sie es uns anhand verschiedener Beispiele verstehen.
1. Die folgende Anweisung wählt zunächst die Spalte „Jahr“, „Nord“ und „Süd“ als Basisdaten für die Pivotierung aus. Erstellen Sie dann mithilfe der abgeleiteten Tabelle ein temporäres Ergebnis und wenden Sie schließlich den PIVOT-Operator an, um die endgültige Ausgabe zu generieren. Auch diese Ausgabe ist aufsteigend nach Jahr sortiert.
SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS Tab2 ORDER BY Tab2.Year
Das Ausführen dieser Anweisung erzeugt die folgende Ausgabe. Hier können wir das sehen Berechnete Summe der Umsätze der Nord- und Südregion entsprechend den Jahreswerten .
2. Dies ist ein weiteres Beispiel, in dem wir die Summe der Verkäufe für jedes Jahr entsprechend den Regionswerten berechnen:
SELECT Region, 2010, 2011, 2012 FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN (2010, 2011, 2012)) AS Tab2 ORDER BY Tab2.Region;
Die Ausführung dieser Anweisung wird einen Fehler erzeugen weil wir den numerischen Wert nicht direkt als Spaltennamen angeben können.
Mit SQL Server können wir dieses Problem jedoch vermeiden, indem wir vor jedem ganzzahligen Wert Klammern verwenden. Die aktualisierte Anweisung wird im folgenden Codeausschnitt angezeigt:
SELECT Region, [2010], [2011], [2012] FROM (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR [Year] IN ([2010], [2011], [2012])) AS Tab2 ORDER BY Tab2.Region;
Diese Anweisung wurde erfolgreich ausgeführt und zeigt die berechnete Summe der Verkäufe für jedes Jahr entsprechend den Regionswerten an:
3. Das vorherige Beispiel zum Abrufen einer PIVOT-Tabelle ist hilfreich, wenn wir alle möglichen PIVOT-Spaltenwerte kennen. Angenommen, im kommenden Jahr wird die Anzahl der Spalten erhöht. Wenn wir das vorherige Beispiel betrachten, haben wir die Jahre 2010, 2011 und 2012 als PIVOT-Spalten. Es gibt jedoch keine Garantie dafür, dass sich diese Spalten in Zukunft nicht ändern. Was passiert, wenn wir Daten aus den Jahren 2013 oder 2014 haben, oder vielleicht sogar noch mehr? In solchen Fällen müssen wir verwenden dynamische PIVOT-Tabelle Abfragen, um dieses Problem zu beheben.
Die dynamische PIVOT-Tabellenabfrage kapselt das gesamte PIVOT-Skript in einer gespeicherten Prozedur. Dieses Verfahren bietet anpassbare Optionen, die es uns ermöglichen, unsere Anforderungen durch Änderung einiger parametrisierter Werte zu ändern.
Der folgende SQL-Code erläutert die Funktionsweise der dynamischen PIVOT-Tabelle. In diesem Skript haben wir zunächst alle eindeutigen Werte aus der PIVOT-Spalte abgerufen und dann eine SQL-Anweisung zur Ausführung mit der PIVOT-Abfrage zur Laufzeit geschrieben. Sehen wir uns die Ausgabe nach der Ausführung dieses Skripts an:
CREATE PROCEDURE DynamicPivotTable @PivotColumn NVARCHAR(255), @PivotList NVARCHAR(255) AS BEGIN DECLARE @Query NVARCHAR(MAX); SET @Query = N' SELECT * FROM (SELECT [Region], [Year], [Sales] FROM pivot_demo) AS tab1 PIVOT (SUM([Sales]) FOR ['+@Pivot_Column+'] IN ('+@Pivot_List+')) AS PivotTable'; EXEC(@Query) END
In diesem Skript haben wir zwei parametrisierte Variablen erstellt. Seine Beschreibung ist unten angegeben:
@PivotColumn : Diese Variable übernimmt den Namen der Spalte aus der Originaltabelle, in der die PIVOT-Tabelle erstellt wird. Zum Beispiel Hier werden in der Spalte „Region“ alle in den Spalten verfügbaren Regionen angezeigt.
@PivotList : Diese Variable übernimmt die Spaltenliste, die wir als Ausgabespalte in der PIVOT-Tabelle anzeigen möchten.
Ausführung einer dynamischen gespeicherten Prozedur
Nach der erfolgreichen Erstellung der dynamischen gespeicherten Prozedur können wir sie ausführen. Mit der folgenden Anweisung wird die dynamische gespeicherte Prozedur aufgerufen, um die PIVOT-Tabelle zur Laufzeit anzuzeigen:
EXEC DynamicPivotTable N'Region', N'[North], [South]'
Hier haben wir nun den Spaltennamen angegeben ' Region ' als ersten Parameter und die PIVOT-Spaltenliste als zweiten Parameter. Wenn Sie das Skript ausführen, wird die folgende Ausgabe angezeigt:
Jetzt können wir in Zukunft zur Laufzeit weitere Spalten hinzufügen, um die PIVOT-Tabelle anzuzeigen, was mit den ersten beiden Beispielen nicht möglich ist.
UNPIVOT-Operator
Es handelt sich um die umgekehrte Methode des PIVOT-Operators in SQL Server. Es mans dieser Operator führt das aus entgegengesetzte Bedienung von PIVOT durch Konvertieren von Daten von Spalten in Zeilen. Der UNPIVOT-Operator dreht auch die PIVOT-Tabelle in die reguläre Tabelle. Es wurde erstmals in der SQL Server 2005-Version eingeführt.
Syntax
Die folgende Syntax veranschaulicht den UNPIVOT in SQL Server:
SELECT (column_names) FROM (table_name) UNPIVOT ( Aggregate_function (column to be aggregated) FOR PivotColumn IN (pivot column values) ) AS (alias_name)
Beispiel
Lassen Sie uns anhand von Beispielen verstehen, wie die PIVOT-Operation UNPIVOT ausgeführt wird. Wir erstellen zunächst eine Originaltisch und PIVOT-Tisch und dann den UNPIVOT-Operator auf diese Tabelle angewendet.
Der folgende Codeausschnitt deklariert zunächst eine temporäre Tabellenvariable @Tab:
DECLARE @Tab TABLE ( Year int, North varchar(45), South varchar(45) )
Als nächstes fügen wir Werte wie folgt in diese Tabelle ein:
INSERT INTO @Tab SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ORDER BY PivotTable.Year
Jetzt können wir die UNPIVOT-Operation mit der folgenden Anweisung ausführen:
SQL auswählen als
SELECT Region, Year, Sales FROM @Tab t UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Das Ausführen des Code-Snippets gibt die folgende Ausgabe zurück:
Das folgende Code-Snippet ist ein weiteres Beispiel dafür, wie innerhalb einer einzelnen Abfrage zunächst ein PIVOT-Vorgang und dann ein UNPIVOT-Vorgang für dieselbe Tabelle ausgeführt werden:
SELECT Region, Year, Sales FROM ( SELECT Year, North, South FROM (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1 PIVOT (SUM(Sales) FOR Region IN (North, South)) AS PivotTable ) P --Perform UNPIVOT Operation UNPIVOT ( Sales FOR Region IN (North, South) ) AS UnpivotTable
Beim Ausführen des Codeausschnitts wird dieselbe Ausgabe angezeigt:
HINWEIS: Der UNPIVOT-Vorgang ist eine umgekehrte Operation des PIVOT-Vorgangs, aber keine exakte Umkehrung. Da bei der Berechnung des Aggregats durch PIVOT Zeilen zusammengeführt wurden und im Ergebnis viele Zeilen zu einer einzigen Zeile zusammengefasst wurden, kann die UNPIVOT-Operation die Tabelle nicht wie das Original angleichen. Wenn der PIVOT-Operator jedoch nicht viele Zeilen in einer einzigen Zeile zusammenführt, kann der UNPIVOT-Operator die Originaltabelle aus der PIVOT-Ausgabe abrufen.
Abschluss
Dieser Artikel gibt einen vollständigen Überblick über PIVOT- und UNPIVOT-Operatoren in SQL Server und konvertiert einen Tabellenausdruck in einen anderen. Man sollte nie vergessen, dass UNPIVOT die umgekehrte Operation von PIVOT ist, aber nicht die exakte Umkehrung des PIVOT-Ergebnisses.