Verwenden von PIVOT und UNPIVOT
Aktualisiert: 17. Juli 2006
Verwenden Sie die relationalen Operatoren PIVOT und UNPIVOT, um einen Tabellenwertausdruck in eine andere Tabelle zu ändern. Mit dem PIVOT-Operator wird ein Tabellenwertausdruck umgesetzt, indem die eindeutigen Werte einer Spalte des Ausdrucks in mehrere Spalten der Ausgabe versetzt werden und dabei gegebenenfalls Aggregationen für verbliebene Spaltenwerte erfolgen, die in der endgültigen Ausgabe erwünscht sind. Der UNPIVOT-Operator führt den umgekehrten Vorgang aus, d. h. er setzt Spalten eines Tabellenwertausdrucks in Spaltenwerte zurück.
.gif "ms177410.note(de-de,SQL.90).gif") Hinweis: |
|---|
| Wenn PIVOT und UNPIVOT für Datenbanken verwendet werden, die auf Microsoft SQL Server 2005 aktualisiert werden, muss der Kompatibilitätsgrad der Datenbank auf 90 festgelegt sein. Informationen zum Festlegen des Kompatibilitätsgrades der Datenbank finden Sie unter sp_dbcmptlevel (Transact-SQL). |
Die von PIVOT bereitgestellte Syntax ist einfacher und lesbarer als die Syntax, die andernfalls durch eine komplexe Reihe von SELECT...CASE-Anweisungen angegeben werden müsste. Eine vollständige Beschreibung der Syntax für PIVOT finden Sie unter FROM (Transact-SQL).
Nachfolgend ist kommentierte Syntax für PIVOT aufgeführt.
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>
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>
Elementares Beispiel für PIVOT
Im folgenden Codebeispiel wird eine zweispaltige Tabelle mit vier Zeilen erstellt.
USE AdventureWorks ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture
Dies ist das Resultset.
DaysToManufacture AverageCost
0 5.0885
1 223.88
2 359.1082
4 949.4105
Es sind keine Produkte mit drei DaysToManufacture definiert.
Im folgenden Code wird dasselbe Ergebnis pivotiert angezeigt, sodass die DaysToManufacture-Werte als Spaltenüberschriften verwendet werden. Es wird eine Spalte für drei [3] Tage bereitgestellt, auch wenn die Ergebnisse NULL betragen.
-- Pivot table with one row and five columns
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
Dies ist das Resultset.
Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
Komplexes PIVOT-Beispiel
Ein häufiges Szenario, in dem sich PIVOT als nützlich erweisen kann, ist das Generieren von Kreuztabellenberichten zum Zusammenfassen von Daten. Nehmen Sie z. B. an, Sie möchten die PurchaseOrderHeader-Tabelle in der AdventureWorks-Beispieldatenbank abfragen, um die Anzahl an von bestimmten Mitarbeitern aufgenommenen Bestellungen zu bestimmen. Mit der folgenden Abfrage wird dieser Bericht geordnet nach Verkäufern bereitgestellt:
USE AdventureWorks;
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID
Dies ist ein Auszug aus dem Resultset.
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
1 4 3 5 4 4
2 4 1 5 5 5
3 4 3 5 4 4
4 4 2 5 5 4
5 5 1 5 5 5
Die von dieser untergeordneten SELECT-Anweisung zurückgegebenen Ergebnisse werden in die EmployeeID-Spalte pivotiert.
SELECT PurchaseOrderID, EmployeeID, VendorID
FROM PurchaseOrderHeader
Dies bedeutet, dass die von der EmployeeID-Spalte zurückgegebenen eindeutigen Werte ihrerseits zu Feldern im endgültigen Resultset werden. Das Ergebnis ist eine Spalte für jede EmployeeID-Nummer, die in der PIVOT-Klausel angegeben war: In diesem Fall die Mitarbeiter 164, 198, 223, 231 und 233. Die PurchaseOrderID-Spalte dient als Wertspalte, für die die in der endgültigen Ausgabe zurückgegebenen Spalten, die auch als Gruppierungsspalten bezeichnet werden, gruppiert sind. In diesem Fall werden die Gruppierungsspalten durch die COUNT-Funktion aggregiert. Beachten Sie, dass eine Warnmeldung darauf hinweist, dass eventuell vorhandene NULL-Werte, die sich in der PurchaseOrderID-Spalte befinden, bei der Berechnung der COUNT-Funktion für die einzelnen Mitarbeiter nicht berücksichtigt werden.
| Wichtig: |
|---|
| Beim Verwenden der Aggregatfunktionen mit PIVOT werden eventuell vorhandene NULL-Werte in der Wertespalte bei der Berechnung der Aggregation nicht berücksichtigt. |
UNPIVOT führt nahezu den entgegengesetzten Vorgang zu PIVOT aus, indem dabei die Spalten zu Zeilen umgesetzt werden. Angenommen, die im vorherigen Beispiel erstellte Tabelle wurde in der Datenbank als pvt gespeichert, und Sie möchten nun die Spalten-IDs Emp1, Emp2, Emp3, Emp4 und Emp5 zu Zeilenwerten umsetzen, sodass sie einem bestimmten Verkäufer entsprechen. Dies bedeutet, dass Sie zwei zusätzliche Spalten identifizieren müssen. Die Spalte, die die umzusetzenden Spaltenwerte erhalten soll (Emp1, Emp2,...), wird Employee genannt, und die Spalte, die die Werte erhalten soll, die sich derzeit unter den umzusetzenden Spalten befinden, wird Orders genannt. Diese Spalten entsprechen jeweils pivot_column und value_column in der Transact-SQL-Definition. Im Folgenden wird die Abfrage aufgeführt:
--Create the table and insert values as portrayed in the previous example.
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
Dies ist ein Auszug aus dem Resultset.
VendorID Employee Orders
1 Emp1 4
1 Emp2 3
1 Emp3 5
1 Emp4 4
1 Emp5 4
2 Emp1 4
2 Emp2 1
2 Emp3 5
2 Emp4 5
2 Emp5 5
...
Beachten Sie, dass UNPIVOT nicht das exakte Gegenteil von PIVOT ist. PIVOT führt eine Aggregation aus, d. h. der Operator führt ggf. mehrere Zeilen in einer einzigen Zeile der Ausgabe zusammen. UNPIVOT ergibt keine Reproduktion des ursprünglichen Tabellenwertausdrucks, da Zeilen zusammengeführt wurden. Außerdem verschwinden NULL-Werte in der Eingabe von UNPIVOT in der Ausgabe, auch wenn beim ursprünglichen PIVOT-Vorgang NULL-Werte vorhanden waren.
Für die Sicht Sales.vSalesPersonSalesByFiscalYears in der AdventureWorks-Beispieldatenbank wird PIVOT verwendet, um den Gesamtumsatz jedes Vertriebsmitarbeiters pro Geschäftsjahr zurückzugeben. Damit Sie das Skript in SQL Server Management Studio anzeigen können, suchen Sie die Sicht im Objekt-Explorer im Ordner Sichten für die AdventureWorks-Datenbank. Klicken Sie mit der rechten Maustaste auf den Namen der Sicht, und wählen Sie Skript für Sicht als aus.
Siehe auch
Andere Ressourcen
FROM (Transact-SQL)
CASE (Transact-SQL)
Hilfe und Informationen
Informationsquellen für SQL Server 2005
Änderungsverlauf
| Version | Verlauf |
|---|---|
17. Juli 2006 |
|