In diesem Artikel werden die SQL Pivot- und SQL Unpivot-Operatoren erläutert und erläutert, wie sie zum Transponieren von SQL Server-Daten nützlich sein können. Außerdem werden sowohl statische als auch dynamische Methoden zur Verwendung relationaler PIVOT- und UNPIVOT-Operatoren erläutert, mit denen aggregierte eindeutige Werte als Spalten in der Ergebnismenge transformiert werden können, indem alle Spaltenwerte in der PIVOT IN-Klausel angegeben werden.
SQL Server Pivot Einführung
Es gibt verschiedene Möglichkeiten, ein Dataset von Zeilen in Spalten und von Spalten in Zeilen zu transponieren. SQL Pivot ist eine der Techniken, die das Transponieren von Zeilen in Spalten ermöglicht und dabei mögliche Aggregationen durchführt. SQL PIVOT und SQL UNPIVOT relationale Operatoren transponieren zweidimensionale Daten mit Tabellenwert in eine andere Form von Daten. SQL PIVOT transponiert einen Tabellenausdruck aus einem eindeutigen Satz von Werten aus einer Spalte in mehrere Spalten in der Ausgabe und führt Aggregationen durch. SQL UNPIVOT führt die entgegengesetzte Operation von SQL PIVOT durch, indem die Spalten eines Tabellenausdrucks in Spaltenwerte umgewandelt werden.
In den meisten Fällen reicht die statische Schwenktechnik aus, um die Geschäftsanforderungen zu erfüllen. Zum Beispiel die monatliche Umsatzprognose, die jährliche Umsatzaufteilung, die vierteljährliche Umsatzaggregation usw., wobei die Spalten IN der Klausel statisch bleiben. In einigen anderen Fällen benötigen wir detaillierte Details, und der Tabellenwert-Ausdruck ist dynamischer und die ganze Zeit, wenn ein neuer Satz des Ausdrucks in der Tabelle enthalten ist, ist ein dynamischer PIVOT die beste Wahl.
Hinweis: In der obigen Darstellung können wir sehen, dass die Spaltenwerte während des Schwenkvorgangs von vertikal nach horizontal gedreht werden und das AUFHEBEN des Schwenkens wie das Drehen von horizontal nach vertikal ist.
Syntax
SELECT <non-pivoted column>,
AS <column alias>,
AS <column alias>,
…
AS <column alias>
FROM
(
<SELECT QUERY>)
AS <Alias for temporary data set>
PIVOT
(
<Aggregate function>( Aggregate column)
FOR
IN ( , ,
… )
) AS <Alias der Pivottabelle>
< ORDER BY-Klausel>;
Erste Schritte mit SQL Pivot
Wir beginnen mit dem Pivotieren und Un-Pivotieren der Daten. Inzwischen wissen Sie, was PIVOT- und UNPIVOT-Operatoren in SQL Server sind. In einfachen Worten, es ist nur eine andere Möglichkeit, den Prozess der Umwandlung verschiedener Zeilenwerte in eigene Spalten zu beschreiben, der als Pivoting bezeichnet wird. Und der Prozess, Spalten in Zeilen umzuwandeln, wird als Un-Pivoting bezeichnet.
Beispiel 1: So führen Sie eine SQL-Pivot-Operation aus
Stellen wir uns den Beispieldatensatz vor, der aus der AdventureWorks2014-Datenbank abgeleitet wurde. In diesem Fall haben Sie gerade einen einfachen Datensatz mit der Spalte SalesYear entlang des einen quantitativen Wertes TotalSales .
Nun ist der Datensatz zum Pivotieren bereit. Sie haben SalesYear in der ersten Spalte, TotalSales in der zweiten Spalte. Wenn Sie die Daten transponieren möchten, müssen Sie jedes einzelne Jahr in der Spalte verwenden, und diese Spalten werden zur Kopfzeile der Pivot-Spalten. 2011 wird also zur ersten Spalte, 2012 zur eigenen Spalte und so weiter. Die tatsächlichen Werte aus der nächsten Spalte Totalsales bleiben bei den Pivot-Spaltenüberschriften. Nun würde der schwenkbare Tisch wie folgt aussehen.
|
1
2
3
4
5
6
7
8
9
10
|
WÄHLEN SIE * AUS
(
JAHR AUSWÄHLEN(SOH.OrderDate) als Verkaufsjahr,
SOH.Zwischensumme als TotalSales
VOM Umsatz.SalesOrderHeader SOH
Vertrieb BEITRETEN.SalesOrderDetail SOD AUF SOH.SalesOrderId = SOD.SalesOrderId
) ALS Sales
PIVOT (SUMME(TotalSales)
FÜR Sales( IN (,,,))
als PVT
|
Das folgende Bild zeigt die Eingabedaten, die Pivot-Syntax, SQL-Pivot und die Ausgabeergebnisse.
Beispiel 2: So führen Sie eine SQL-Unpivot-Operation aus
Lassen Sie uns nun in die andere Richtung schauen, Un-Pivoting, was so einfach ist wie von horizontal nach vertikal zu wechseln. In diesem Fall würden Sie alle ausgewählten unterschiedlichen Spalten verwenden und diese Spaltenüberschriften in eigene Zeilen umwandeln. Schwenken ist wie Drehen von vertikal nach horizontal. Und das Schwenken ist wie das Drehen von horizontal nach vertikal.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
Verkaufsjahr AUSWÄHLEN,
Gesamtumsatz
AUS dem
(
WÄHLEN SIE *
AUS dem
(
JAHR AUSWÄHLEN(SOH.OrderDate) ALS Verkaufsjahr,
SOH.Zwischensumme ALS TotalSales
VOM Umsatz.SalesOrderHeader SOH
Vertrieb BEITRETEN.SalesOrderDetail SOD AUF SOH.SalesOrderId = SOD.SalesOrderId
) ALS Sales PIVOT(SUMME(TotalSales) FÜR SalesYear IN(,
,
,
)) ALS PVT
) T UNPIVOT(TotalSales FÜR SalesYear IN(,
,
,
)) ALS upvt;
|
Im folgenden Beispiel wird das Pivoted-Dataset zum Aufheben des Pivots der Werte verwendet. Das Wichtigste dabei ist, dass dies bei der Transformation einfach zu bewerkstelligen war, da wir als Zwischenschritt eine Pivot-Tabelle erstellen und dann die Transformation mit der Operation unpivot durchführen konnten.
etrennte Werte von Zeilen. Der Unpivot-Operator teilt die aggregierten Ergebnisse nicht auf.
Beispiel 3: So erhalten Sie vierteljährliche Umsätze mit SQL Pivot operation
Sehen wir uns einige weitere Beispiele an, um die Pivoting-Konzepte besser zu verstehen. In diesem Fall sehen wir, wie vierteljährliche Verkaufsdaten basierend auf den vierteljährlichen Verkäufen aggregiert werden.
Bevor wir mit der Lösung beginnen, wird immer empfohlen, an der Erstellung eines Datensatzes für die PIVOT-Operation zu arbeiten.
Das folgende Beispiel zeigt die aggregierten monatlichen Umsätze, die über das Verkaufsjahr verteilt sind.
|
1
2
3
4
5
6
7
8
9
|
JAHR AUSWÄHLEN(SOH.Bestelldatum) ALS VERKAUFSJAHR,
MONAT(SOH.OrderDate) ALS SalesMonth,
SUMME(SOH.Zwischensumme) ALS TotalSales
VOM Umsatz.SalesOrderHeader SOH
Vertrieb BEITRETEN.SalesOrderDetail SOD AUF SOH.SalesOrderId = SOD.SalesOrderId
GRUPPE NACH JAHR(SOH.OrderDate),
MONAT(SOH.OrderDate)
BESTELLUNG NACH JAHR(SOH.OrderDate),
MONAT(SOH.OrderDate);
|
Die folgende Ausgabe zeigt die Basisdaten
Sobald Sie mit der Basisdaten können Sie den PIVOT-Operator anwenden. Im folgenden Beispiel akzeptiert die IN-Klausel ein nicht numerisches Zeichen, das basierend auf dem DATEPART-Wert generiert wurde. Der DATEPART-Wert wird mit ‚Q‘ verkettet und dann werden die Werte der IN-Klausel des PIVOT-Operators zugeführt.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
Verkaufsjahr AUSWÄHLEN,
ISNULL(, 0) ALS Q1,
ISNULL(, 0) ALS Q2,
ISNULL(, 0) ALS Q3,
ISNULL(, 0) ALS Q4,
(ISNULL(, 0) + ISNULL, 0) + ISNULL(, 0) + ISNULL(, 0)) SalesYTD
VON
(
JAHR AUSWÄHLEN(SOH.OrderDate) ALS SalesYear,
CAST(‚Q’+CAST(DATEPART(QUARTAL, SOH.OrderDate) ALS VARCHAR(1)) ALS VARCHAR(2)) Quartale,
SOH.Zwischensumme ALS TotalSales
VOM Umsatz.SalesOrderHeader SOH
Vertrieb BEITRETEN.SalesOrderDetail SOD AUF SOH.SalesOrderId = SOD.SalesOrderId
) ALS Daten-PIVOT(SUMME(TotalSales) FÜR Quartale IN(,
,
,
)) ALS pvt
ORDER BY SalesYear;
|
Die folgende Ausgabe ist eine vierteljährliche Aufteilung der Verkaufsdaten
Beispiel 4: Wie holen Sie sich monatliche Umsätze mit einer SQL-Pivot-Operation
Lassen Sie uns einen Blick in ein anderes Beispiel werfen, um zu sehen, wie der Umsatz die Daten basierend auf jedem Monat aufteilt. Im folgenden Beispiel werden die IN-Klauselwerte als Teil der DATEPART-Funktion generiert. Die DATEPART-Werte werden der IN-Klausel des PIVOT-Operators zugeführt.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
Verkaufsjahr AUSWÄHLEN,
Isnull(, 0) als Jan,
Isnull(, 0) als Feb,
Isnull(, 0) als Mar,
ISNULL(, 0) als Apr,
isnull(, 0) als Mai,
isnull(, 0) als Jun ,
isnull(, 0) als Jul,
isnull(, 0) als Aug,
isnull(, 0) als Sep,
ISNULL(, 0) als Okt,
isnull(, 0) als Nov,
isnull(, 0) als Dez,
(isnull(, 0) + isnull(, 0) + NULL (, 0) + Null(, 0) + Null(, 0) + Null(, 0) + NULL(, 0) + NULL(, 0) + Null(, 0) + Null(, 0) +Null (, 0) +Null (, 0) + Null (, 0) + Null (, 0) + Null (, 0) + ISNULL(, 0) + ISNULL(, 0) + ISNULL(, 0)) SalesYTD
AUS dem
(
JAHR AUSWÄHLEN(SOH.OrderDate) ALS SalesYear,
DATEPART(MONAT, SOH.OrderDate) Monate,
SOH.Zwischensumme ALS TotalSales
VOM Umsatz.SalesOrderHeader SOH
Vertrieb BEITRETEN.SalesOrderDetail SOD AUF SOH.SalesOrderId = SOD.SalesOrderId
) ALS Daten-PIVOT(SUMME(TotalSales) FÜR Monate IN(,
,
,
,
,
,
,
,
,
,
,
)) ALS pvt;
|
Die folgende Ausgabe ist eine monatliche Aufteilung der Verkaufsdaten
Beispiel 5: Wie man monatliche Verkäufe mit dynamischer SQL-Pivot-Operation erhält
Lassen Sie uns die monatliche Aufteilung der Verkaufsdaten mit dynamischer schwenken. Bisher haben wir statische Pivot-Operationen diskutiert. Um eine statische Pivot-Operation in eine dynamische zu konvertieren, müssen wir die fest codierten Werte aus der IN-Klausel entfernen. Rufen Sie zuerst den eindeutigen Ausdruck aus der Datenmenge ab und bereiten Sie dann eine Zeichenfolge vor, indem Sie alle Ausdrücke verketten. Im folgenden Beispiel wird @columnname verwendet, um den gesamten Ausdruck zu verketten. Der verkettete Ausdruck wird der Pivot IN-Klausel zugeführt. Der Rest ist eine einfache Konvertierung von statischem SQL in ein dynamisches SQL und Aufruf von @DML mit der gespeicherten Prozedur sp_executesql.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
ANGABE VON @dml ALS NVARCHAR(MAX)
DEKLARIERT @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ‚,‘,“)
+ QUOTENAME(Monate)
FROM (SELECT DISTINCT DATEPART(MONAT, SOH.OrderDate) Monate
VOM Umsatz.SalesOrderHeader SOH
Vertrieb BEITRETEN.SalesOrderDetail SOD AUF SOH.SalesOrderId = SOD.SalesOrderId
GRUPPE NACH JAHR(SOH.OrderDate),
DATEPART(MONAT, SOH.OrderDate)) ALS Monate
–Bereiten Sie die PIVOT-Abfrage mit dem dynamischen
SET @dml =
N’SELECT SalesYear, ‚ +@ColumnName + ‚ FROM
(
SELECT YEAR(SOH.OrderDate) ALS SalesYear,
DATEPART(MONAT, SOH.OrderDate) Monate,
SUMME(SOH.Zwischensumme) ALS TotalSales
VOM Umsatz.SalesOrderHeader SOH
Vertrieb BEITRETEN.SalesOrderDetail SOD AUF SOH.SalesOrderId = SOD.SalesOrderId
GRUPPE NACH JAHR(SOH.OrderDate),
DATEPART(MONAT, SOH.OrderDate)) ALS T
PIVOT(SUMME(TotalSales)
FÜR Monate IN (‚ + @ColumnName + ‚)) ALS PVTTable‘
–Print @DynamicPivotQuery
–Führe die dynamische Pivot-Abfrage aus
EXEC sp_executesql @dml
|
Das ist alles für jetzt …
Einpacken
Bisher haben wir die grundlegenden Konzepte und einige Beispiele von SQL PIVOT und SQL UNPIVOT demonstriert.
Das Beispiel basiert auf der SQL Server adventureworks2014-Datenbank. Sie können es versuchen und alle Proben selbst testen. Wenn Sie Fragen haben, zögern Sie nicht, unten zu kommentieren …
- Autor
- Aktuelle Beiträge
Meine Spezialität liegt im Design & Implementierung von Hochverfügbarkeitslösungen und plattformübergreifender DB-Migration. Die Technologien, an denen derzeit gearbeitet wird, sind SQL Server, PowerShell, Oracle und MongoDB.
Alle Beiträge von Prashanth Jayaram anzeigen
- Ein kurzer Überblick über die Datenbanküberwachung in SQL – 28. Januar 2021
- Einrichten der Azure-Datensynchronisierung zwischen Azure SQL-Datenbanken und lokaler SQL Server – 20. Januar 2021
- Ausführen von Import-/Exportvorgängen für Azure SQL-Datenbanken mit PowerShell – 14. Januar 2021