VON - Verwenden von PIVOT und UNPIVOTFROM - Using PIVOT and UNPIVOT

DIESES THEMA GILT FÜR: jaSQL Server (ab 2008)jaAzure SQL-DatenbankjaAzure SQL Data Warehouse jaParallel Data Warehouse THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Sie können die PIVOT und UNPIVOT relationale Operatoren, um eine Tabellenwert-Ausdruck in einer anderen Tabelle zu ändern.You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOTAktivieren die eindeutigen Werte einer Spalte des Ausdrucks in mehrere Spalten in der Ausgabe dreht eine tabellenwertausdrucks, Aggregationen und führt, sind sie für verbliebene Spaltenwerte erforderlich, die in der endgültigen Ausgabe erwünscht sind.PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOTFührt den entgegengesetzten Vorgang zu PIVOT er setzt Spalten eines tabellenwertausdrucks in Spaltenwerte zurück.UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

Die Syntax für PIVOT bietet ist einfacher und besser lesbar als die Syntax, die andernfalls in eine komplexe Reihe von angegeben werden kann SELECT...CASE Anweisungen.The syntax for PIVOT provides is simpler and more readable than the syntax that may otherwise be specified in a complex series of SELECT...CASE statements. Eine vollständige Beschreibung der Syntax für PIVOT, finden Sie unter aus (Transact-SQL).For a complete description of the syntax for PIVOT, see FROM (Transact-SQL).

SyntaxSyntax

Die folgende Syntax werden zusammengefasst, wie die PIVOT Operator.The following syntax summarizes how to use the PIVOT operator.

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>;  

HinweiseRemarks

Die Spalten-IDs in der UNPIVOT -Klausel folgen die katalogsortierung.The column identifiers in the UNPIVOT clause follow the catalog collation. Für SQL-DatenbankSQL Database, die Sortierung ist immer SQL_Latin1_General_CP1_CI_AS.For SQL-DatenbankSQL Database, the collation is always SQL_Latin1_General_CP1_CI_AS. Für SQL ServerSQL Server teilweise eigenständige Datenbanken, die Sortierung ist immer Latin1_General_100_CI_AS_KS_WS_SC.For SQL ServerSQL Server partially contained databases, the collation is always Latin1_General_100_CI_AS_KS_WS_SC. Wenn die Spalte mit den anderen Spalten, und klicken Sie dann auf eine Collate-Klausel kombiniert wird (COLLATE DATABASE_DEFAULT) ist erforderlich, um Konflikte zu vermeiden.If the column is combined with other columns, then a collate clause (COLLATE DATABASE_DEFAULT) is required to avoid conflicts.

Elementares Beispiel für PIVOTBasic PIVOT Example

Im folgenden Codebeispiel wird eine zweispaltige Tabelle mit vier Zeilen erstellt.The following code example produces a two-column table that has four rows.

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

Im Folgenden finden Sie das Resultset.Here is the result set.

DaysToManufacture AverageCost
----------------- -----------
0                 5.0885
1                 223.88
2                 359.1082
4                 949.4105

Es sind keine Produkte mit drei DaysToManufacture definiert.No products are defined with three DaysToManufacture.

Im folgenden Code wird dasselbe Ergebnis pivotiert angezeigt, sodass die DaysToManufacture-Werte als Spaltenüberschriften verwendet werden.The following code displays the same result, pivoted so that the DaysToManufacture values become the column headings. Es wird eine Spalte für drei [3] Tage bereitgestellt, auch wenn die Ergebnisse NULL betragen.A column is provided for three [3] days, even though the results are NULL.

-- 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;  

Im Folgenden finden Sie das Resultset.Here is the result set.

Cost_Sorted_By_Production_Days 0           1           2           3           4         
------------------------------ ----------- ----------- ----------- ----------- -----------
AverageCost                    5.0885      223.88      359.1082    NULL        949.4105

Komplexes PIVOT-BeispielComplex PIVOT Example

Ein häufiges Szenario, in dem sich PIVOT als nützlich erweisen kann, ist das Generieren von Kreuztabellenberichten zum Zusammenfassen von Daten.A common scenario where PIVOT can be useful is when you want to generate cross-tabulation reports to summarize data. Nehmen Sie z. B. an, Sie möchten die PurchaseOrderHeader-Tabelle in der AdventureWorks2014-Beispieldatenbank abfragen, um die Anzahl an von bestimmten Mitarbeitern aufgenommenen Bestellungen zu bestimmen.For example, suppose you want to query the PurchaseOrderHeader table in the AdventureWorks2014 sample database to determine the number of purchase orders placed by certain employees. Mit der folgenden Abfrage wird dieser Bericht geordnet nach Verkäufern bereitgestellt:The following query provides this report, ordered by vendor.

USE AdventureWorks2014;  
GO  
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5  
FROM   
(SELECT PurchaseOrderID, EmployeeID, VendorID  
FROM Purchasing.PurchaseOrderHeader) p  
PIVOT  
(  
COUNT (PurchaseOrderID)  
FOR EmployeeID IN  
( [250], [251], [256], [257], [260] )  
) AS pvt  
ORDER BY pvt.VendorID;  

Dies ist ein Auszug aus dem Resultset.Here is a partial result set.

VendorID    Emp1        Emp2        Emp3        Emp4        Emp5  
----------- ----------- ----------- ----------- ----------- -----------
1492        2           5           4           4           4
1494        2           5           4           5           4
1496        2           4           4           5           5
1498        2           5           4           4           4
1500        3           4           4           5           4

Die von dieser untergeordneten SELECT-Anweisung zurückgegebenen Ergebnisse werden in die EmployeeID-Spalte pivotiert.The results returned by this subselect statement are pivoted on the EmployeeID column.

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.This means that the unique values returned by the EmployeeID column themselves become fields in the final result set. 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.Therefore, there is a column for each EmployeeID number specified in the pivot clause: in this case employees 164, 198, 223, 231, and 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.The PurchaseOrderID column serves as the value column, against which the columns returned in the final output, which are called the grouping columns, are grouped. In diesem Fall werden die Gruppierungsspalten durch die COUNT-Funktion aggregiert.In this case, the grouping columns are aggregated by the COUNT function. 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.Notice that a warning message appears that indicates that any null values appearing in the PurchaseOrderID column were not considered when computing the COUNT for each employee.

Wichtig

Wenn Aggregatfunktionen verwendet werden, mit PIVOT, das Vorhandensein der null-Werte in der Wertspalte werden bei der Berechnung der Aggregation nicht berücksichtigt.When aggregate functions are used with PIVOT, the presence of any null values in the value column are not considered when computing an aggregation.

UNPIVOTführt nahezu den entgegengesetzten Vorgang PIVOT, indem Sie Spalten in Zeilen drehen.UNPIVOT performs almost the reverse operation of PIVOT, by rotating columns into rows. 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.Suppose the table produced in the previous example is stored in the database as pvt, and you want to rotate the column identifiers Emp1, Emp2, Emp3, Emp4, and Emp5 into row values that correspond to a particular vendor. Dies bedeutet, dass Sie zwei zusätzliche Spalten identifizieren müssen.This means that you must identify two additional columns. 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.The column that will contain the column values that you are rotating (Emp1, Emp2,...) will be called Employee, and the column that will hold the values that currently reside under the columns being rotated will be called Orders. Diese Spalten entsprechen den Pivot_column und Value_columnbzw. in die Transact-SQLTransact-SQL Definition.These columns correspond to the pivot_column and value_column, respectively, in the Transact-SQLTransact-SQL definition. So sieht die Abfrage aus.Here is the query.

-- 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.Here is a partial result set.

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 ist nicht das exakte Gegenteil von PIVOT.Notice that UNPIVOT is not the exact reverse of PIVOT. PIVOTführt eine Aggregation, und daher fügt mehrere Zeilen zusammen möglich, in eine einzelne Zeile in der Ausgabe.PIVOT performs an aggregation and, therefore, merges possible multiple rows into a single row in the output. UNPIVOTlässt sich nicht im ursprünglichen tabellenwertausdrucks Resultset reproduzieren, da Zeilen zusammengeführt wurden.UNPIVOT does not reproduce the original table-valued expression result because rows have been merged. Darüber hinaus null-Werte in der Eingabe des UNPIVOT nicht mehr in der Ausgabe angezeigt, während möglicherweise stattgefunden haben ursprünglichen null-Werte in der Eingabe vor der PIVOT Vorgang.Besides, null values in the input of UNPIVOT disappear in the output, whereas there may have been original null values in the input before the PIVOT operation.

Die Sales.vSalesPersonSalesByFiscalYears anzeigen in der AdventureWorks2012AdventureWorks2012 -Beispiel Datenbank verwendet PIVOT um den Gesamtumsatz jedes Vertriebsmitarbeiters pro Geschäftsjahr zurückzugeben.The Sales.vSalesPersonSalesByFiscalYears view in the AdventureWorks2012AdventureWorks2012 sample database uses PIVOT to return the total sales for each salesperson, for each fiscal year. Die Ansicht im Skript SQL Server Management StudioSQL Server Management Studioim Objekt-Explorer, suchen Sie die Sicht der Ansichten Ordner für die AdventureWorks2012AdventureWorks2012 Datenbank.To script the view in SQL Server Management StudioSQL Server Management Studio, in Object Explorer, locate the view under the Views folder for the AdventureWorks2012AdventureWorks2012 database. Mit der rechten Maustaste des Ansichtsnamens aus, und wählen Sie dann Skriptansicht als.Right-click the view name, and then select Script View as.

Siehe auchSee Also

AUS (Transact-SQL) FROM (Transact-SQL)
Fall (Transact-SQL)CASE (Transact-SQL)