Teilen über

FROM: Verwenden von PIVOT und UNPIVOT

  • Artikel

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Verwenden Sie die relationalen Operatoren PIVOT und UNPIVOT, um einen Tabellenwertausdruck in einer andere Tabelle zu ändern. Mit dem PIVOT-Operator wird ein Tabellenwertausdruck rotiert, indem die eindeutigen Werte einer Spalte im Ausdruck in mehrere Spalten in der Ausgabe aufgeteilt werden. PIVOT führt auch Aggregationen aus, bei denen sie für alle verbleibenden Spaltenwerte erforderlich sind, die in der endgültigen Ausgabe gesucht werden sollen. UNPIVOT führt den entgegengesetzten Vorgang PIVOTdurch Drehen von Spalten eines Tabellenwertausdrucks in Spaltenwerte aus.

Die Syntax PIVOT ist einfacher und lesbarer als die Syntax, die sonst in einer komplexen Reihe von SELECT...CASE Anweisungen angegeben werden kann. Eine vollständige Beschreibung der Syntax finden PIVOTSie unter FROM-Klausel.

Die Transact-SQL-Codebeispiele in diesem Artikel verwenden die AdventureWorks2022- oder AdventureWorksDW2022-Beispieldatenbank, die Sie von der Homepage Microsoft SQL Server Samples and Community Projects herunterladen können.

Syntax

In diesem Abschnitt wird die Verwendung und der PIVOT UNPIVOT Operator zusammengefasst.

Syntax für den PIVOT Operator.

SELECT [ <non-pivoted column> [ AS <column name> ] , ]
    ...
    [ <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 become column headers>
    IN ( <first pivoted column>
         , <second pivoted column>
         , ... <last pivoted column> )
) AS <alias for the pivot table>
[ <optional ORDER BY clause> ]
[ ; ]

Syntax für den UNPIVOT Operator.

SELECT [ <non-pivoted column> [ AS <column name> ] , ]
    ...
    [ <output column for names of the pivot columns> [ AS <column name> ] , ]
    [ <new output column created for values in result of the source query> [ AS <column name> ] ]
FROM
    ( <SELECT query that produces the data> )
    AS <alias for the source query>
UNPIVOT
(
    <new output column created for values in result of the source query>
FOR <output column for names of the pivot columns>
    IN ( <first pivoted column>
         , <second pivoted column>
         , ... <last pivoted column> )
)
[ <optional ORDER BY clause> ]
[ ; ]

Hinweise

Die Spaltenbezeichner in der UNPIVOT-Klausel folgen der Katalogsortierung.

  • Für Azure SQL-Datenbank ist die Sortierung immer SQL_Latin1_General_CP1_CI_AS.

  • Bei teilweise eigenständigen SQL Server-Datenbanken wird immer die Sortierung Latin1_General_100_CI_AS_KS_WS_SC verwendet.

Wenn die Spalte mit anderen Spalten kombiniert wird, ist eine COLLATE-Klausel (COLLATE DATABASE_DEFAULT) erforderlich, um Konflikte zu vermeiden.

In Microsoft Fabric- und Azure Synapse Analytics-Pools schlagen Abfragen mit PIVOT Operator fehl, wenn die GROUP BY Ausgabe der Nicht-Pivot-Spalte von PIVOT. Entfernen Sie als Problemumgehung die Nichtpivot-Spalte aus der GROUP BY. Abfrageergebnisse sind identisch, da diese GROUP BY Klausel ein Duplikat ist.

Einfaches Beispiel für PIVOT

Im folgenden Codebeispiel wird eine zweispaltige Tabelle mit vier Zeilen erstellt.

USE AdventureWorks2022;
GO

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

Hier ist das Resultset.

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

Es werden keine Produkte mit dem Wert für 3 DaysToManufacture.

Im folgenden Code wird dasselbe Ergebnis pivotiert angezeigt, sodass die DaysToManufacture-Werte als Spaltenüberschriften verwendet werden. Eine Spalte wird für drei ([3]) Tage bereitgestellt, auch wenn die Ergebnisse vorliegen NULL.

-- Pivot table with one row and five columns
SELECT 'AverageCost' AS CostSortedByProductionDays,
    [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;

Hier ist das Resultset.

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

Komplexes Beispiel für PIVOT

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

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.

SELECT PurchaseOrderID,
    EmployeeID,
    VendorID
FROM PurchaseOrderHeader;

Die von der Spalte EmployeeID zurückgegebenen eindeutigen Werte werden zu Feldern im endgültigen Resultset. Daher gibt es eine Spalte für jede EmployeeID Zahl, die in der Pivotklausel angegeben ist, also Mitarbeiter 250, , 251, , 256, und 260 257in diesem Beispiel. 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. Es wird eine Warnmeldung angezeigt, die angibt, dass alle null-Werte, die in der PurchaseOrderID Spalte angezeigt werden, beim Berechnen der COUNT für jeden Mitarbeiter nicht berücksichtigt wurden.

Wichtig

Wenn Aggregatfunktionen mit PIVOTverwendet werden, wird das Vorhandensein von Nullwerten in der Wertspalte beim Berechnen einer Aggregation nicht berücksichtigt.

UNPIVOT-Beispiel

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. Daher müssen Sie zwei zusätzliche Spalten identifizieren.

Die Spalte, die die Spaltenwerte enthält, die Sie drehen (Emp1, usw.) werden aufgerufenEmployee, und die Spalte, die die Werte enthält, die derzeit unter den gedrehten Spalten vorhanden sind, wird aufgerufenOrdersEmp2. Diese Spalten entsprechen jeweils pivot_column und value_column in der Transact-SQL-Definition. Hier finden Sie die Abfrage:

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

UNPIVOT ist nicht die genaue Umkehr von PIVOT. PIVOT führt eine Aggregation aus, d.h., der Operator führt mehrere Zeilen in einer einzigen Zeile der Ausgabe zusammen. UNPIVOT gibt das ursprüngliche Ergebnis des Tabellenwertausdrucks nicht wieder, da Zeilen zusammengeführt wurden. Außerdem werden NULL die Werte in der Eingabe UNPIVOT in der Ausgabe ausgeblendet. Wenn die Werte ausgeblendet werden, wird gezeigt, dass vor dem Vorgang möglicherweise originale NULL Werte in der PIVOT Eingabe vorhanden waren.

Für die Sicht Sales.vSalesPersonSalesByFiscalYears in der AdventureWorks2022-Beispieldatenbank wird PIVOT verwendet, um den Gesamtumsatz jedes Vertriebsmitarbeiters pro Geschäftsjahr zurückzugeben. Um die Sicht in SQL Server Management Studio skripten zu können, suchen Sie diese im Objekt-Explorer im Ordner Sichten für die AdventureWorks2022-Datenbank. Klicken Sie mit der rechten Maustaste auf den Namen der Sicht, und klicken Sie auf Script View as (Skript für Sicht als).

Zugehöriger Inhalt