Visualisieren Sie eine Situation, in der die Daten in einer anderen Ausrichtung angezeigt werden sollen, als die Daten gespeichert werden, mit den Bedingungen für das Zeilen- und Spaltenlayout. Der Prozess der Umwandlung von zeilenbasierten in spaltenbasierte Daten wird als Pivot bezeichnet. Die PIVOT- und UNPIVOT-Operatoren in SQL Server helfen dabei, die Datenausrichtung von spaltenorientiert zu zeilenorientiert und umgekehrt zu ändern. Dies geschieht, indem die in einer Spalte enthaltenen Werte zu einer Liste mit unterschiedlichen Werten zusammengeführt und diese Liste dann als Spaltenüberschrift projiziert werden.

PIVOT .operator

Dokumente: https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16

Syntax:

SELECT <non-pivoted column>, [first pivoted column] AS <column name>, [second pivoted column] AS <column name>, ... [last pivoted column] AS <column name> FROM (<SELECT query that produces the data>) AS <alias for the source query> PIVOT ( <aggregation function>(<column being aggregated>) FOR [<column that contains the values that will become column headers>] IN ( [first pivoted column], [second pivoted column], ... [last pivoted column]) ) AS <alias for the pivot table> <optional ORDER BY clause>;

Um es einfacher auszudrücken, um PIVOT zu verwenden, benötigen wir 3 Komponenten in der Syntax:

• In der FROM-Klausel muss immer die Eingabespalte angegeben werden, der PIVOT-Operator verwendet diese Spalten, um zu identifizieren, welche Spalten verwendet werden sollen, um die Daten für die Aggregation zu gruppieren.
• Das in den Feeddaten erscheinende Kommatrennzeichen wird als Spaltenüberschrift für die pivotierten Daten verwendet.
• Mit einer Aggregationsfunktion wie SUM, die zum Durchführen von Berechnungen und Gruppieren von Datensätzen verwendet wird.

Zum Beispiel:

In Anbetracht der folgenden Situation, wenn Sie die durchschnittlichen Kosten für Reparaturtage in den Fällen von 1, 2, 3, 4 Tagen erhalten möchten, lautet die Syntax bei normaler Verwendung von GROUP BY wie folgt:

USE AdventureWorks2019 ; GO SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost FROM Production.Product GROUP BY DaysToManufacture;

Dies führt zu einem fehlenden DaysToManufacturing-Datensatz von 3, da bei 3 COST-Preisen nicht gemittelt werden kann, da keine Daten vorhanden sind. Dieses Problem kann über PIVOT wie folgt behandelt werden:

SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, [0], [1], [2], [3], [4] FROM ( SELECT DaysToManufacture, StandardCost FROM Production.Product ) AS SourceTable PIVOT ( AVG(StandardCost) FOR DaysToManufacture IN ([0], [1], [2], [3], [4]) ) AS PivotTable;

Beispiel 2:

Rufen Sie die Top 5 SalesYTD-Gesamtsummen der Sales.SalesTerritory-Tabelle wie folgt ab:

SELECT TOP 5 SUM(SalesYTD) AS TotalSalesYTD, Name FROM Sales.SalesTerritory GROUP BY Name

Und da die Daten klein sind, zeigen sie nur den Wert jedes Bereichs an, sodass wir die Daten horizontal verteilen können, sodass die Datenbank beim Abfragen der Datenbank nur eine Zeile zurückgibt, wodurch die Fähigkeit zum Lesen und Analysieren für die Datenbankabfrage erhöht wird Person:

SELECT TOP 5 'TotalSalesYTD' AS GrandTotal, [NorthWest],[NorthEast],[Central],[Southwest],[Southeast] FROM (SELECT TOP 5 Name,SalesYTD FROM Sales.SalesTerritory) AS SourceTable PIVOT (SUM(SalesYTD) FOR Name IN ([NorthWest],[NorthEast],[Central],[Southwest],[Southeast])) AS PivotTable;

Eine große Herausforderung beim Schreiben von Abfragen mit PIVOT ist die Anforderung, eine feste Liste von Spread-Elementen für den PIVOT-Operator bereitzustellen. Dies ist für eine große Anzahl von Spreizelementen nicht machbar oder unpraktisch. Um dies zu umgehen, können Entwickler dynamisches SQL verwenden. Dynamic SQL bietet eine Lösung zum Erstellen eines Zeichenfolgenliterals, das an SQL Server übergeben, als Befehl interpretiert und dann von SQL Server ausgeführt wird.

UNPIVOT .-Operator

Der UNPIVOT- Operator wird als das Gegenteil von PIVOT verstanden, was bedeutet, die Achse von Spalte zu Zeile zu drehen. Unpivot speichert den ursprünglichen Wert nicht, detaillierte Daten gingen während der Aggregation beim Pivotieren verloren. UNPIVOT kann keinen Wert zuweisen, um die ursprünglichen Detaildaten zurückzugeben. Anstatt Zeilen in Spalten umzuwandeln, konvertiert das Ergebnis von unpivot Spalten in Zeilen. SQL Server stellt den UNPIVOT- Operator bereit, um Daten aus Pivot-Tabellen in eine zeilenbasierte Ausrichtung zu konvertieren.

Beim Entpivotieren von Daten werden eine oder mehrere als Quelle definierte Spalten in Zeilen konvertiert. Die Daten in diesen Zeilen werden auf eine oder mehrere Zeilen verteilt oder aufgeteilt, je nachdem, wie viele Spalten entpivotiert werden müssen.

Zum Beispiel:

CREATE TABLE pvt (VendorID INT, Emp1 INT, Emp2 INT, Emp3 INT, Emp4 INT, Emp5 INT); GO INSERT INTO pvt VALUES (1,4,3,5,4,4); INSERT INTO pvt VALUES (2,4,1,5,5,5); INSERT INTO pvt VALUES (3,4,3,5,4,4); INSERT INTO pvt VALUES (4,4,2,5,5,4); INSERT INTO pvt VALUES (5,5,1,5,5,5); GO -- Unpivot the table. SELECT VendorID, Employee, Orders FROM (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM pvt) p UNPIVOT (Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5) )AS unpvt; GO