FROM – Verwenden von PIVOT und UNPIVOT

• 14.10.2019
• 5 Minuten zum Lesen
• • V
  • c
  • M
  • i
  • M
  • +7

Gilt für: SQL Server (alle unterstützten Versionen) Azure SQL-Datenbank Verwaltete Azure SQL-Instanz Azure Synapse Analytics Paralleles Data Warehouse

Sie können die relationalen Operatoren PIVOT und UNPIVOT verwenden Ändern Sie einen Ausdruck mit Tabellenwert in eine andere Tabelle. PIVOT dreht einen Ausdruck mit Tabellenwert, indem die eindeutigen Werte aus einer Spalte im Ausdruck in mehrere Spalten in der Ausgabe umgewandelt werden. Und PIVOT führt Aggregationen aus, bei denen sie für alle verbleibenden Spaltenwerte erforderlich sind, die in der endgültigen Ausgabe gewünscht werden. UNPIVOT führt die entgegengesetzte Operation aus zu PIVOT durch Drehen von Spalten eines Ausdrucks mit Tabellenwert in Spaltenwerte.

Die Syntax für PIVOT ist einfacher und besser lesbar als die sonst möglicherweise vorhandene Syntax angegeben in einer komplexen Reihe von SELECT...CASE -Anweisungen. Eine vollständige Beschreibung der Syntax für PIVOT finden Sie unter FROM (Transact-SQL).

Syntax

Die folgende Syntax fasst die Verwendung des Operators PIVOT zusammen.

Anmerkungen

Die Spaltenkennungen in der Klausel UNPIVOT folgen der Katalogkollatierung. Bei SQL-Datenbanken lautet die Kollatierung immer SQL_Latin1_General_CP1_CI_AS. Bei SQL Server teilweise enthalten Datenbanken lautet die Sortierung immer Latin1_General_100_CI_AS_KS_WS_SC. Wenn die Spalte kombiniert ist Wenn Sie mit anderen Spalten arbeiten, ist eine Sortierklausel (COLLATE DATABASE_DEFAULT) erforderlich, um Konflikte zu vermeiden.

Grundlegendes PIVOT-Beispiel

Das folgende Codebeispiel erzeugt eine zweispaltige Tabelle mit vier Zeilen.

Hier ist die Ergebnismenge.

Mit drei DaysToManufacture sind keine Produkte definiert.

Der folgende Code zeigt dasselbe Ergebnis an, das so gedreht ist, dass die DaysToManufacture -Werte werden zu Spaltenüberschriften. Eine Spalte wird für drei Tage bereitgestellt, obwohl die Ergebnisse NULL sind.

Hier ist die Ergebnismenge

Komplexes PIVOT-Beispiel

Ein häufiges Szenario, in dem PIVOT nützlich sein kann, ist Wenn Sie Kreuztabellenberichte erstellen möchten, um eine Zusammenfassung der Daten zu erhalten. Angenommen, Sie möchten die Tabelle PurchaseOrderHeader in der Beispieldatenbank AdventureWorks2014 abfragen, um die Anzahl der Bestellungen bestimmter Mitarbeiter zu ermitteln. Die folgende Abfrage enthält diesen Bericht, sortiert nach Hersteller.

Hier ist eine Teilergebnismenge.

Die von dieser Unterauswahl zurückgegebenen Ergebnisse Anweisungen werden in der Spalte EmployeeID gedreht.

Die vom wird zu Feldern in der endgültigen Ergebnismenge. Daher gibt es für jede in der Pivot-Klausel angegebene EmployeeID -Nummer eine Spalte: in diesem Fall Mitarbeiter 250, 251, 256, 257 und 260 div id = „c36dcf6b3b“> -Spalte dient als Wertespalte, anhand derer die in der endgültigen Ausgabe zurückgegebenen Spalten, die als Gruppierungsspalten bezeichnet werden, gruppiert werden. In diesem Fall werden die Gruppierungsspalten durch die COUNT -Funktion. Beachten Sie, dass eine Warnmeldung angezeigt wird, die angibt, dass Nullwerte in der Spalte PurchaseOrderID bei der Berechnung der COUNT für jeden Mitarbeiter.

UNPIVOT-Beispiel

UNPIVOT führt fast die umgekehrte Operation von PIVOT aus, indem Spalten in Zeilen gedreht werden. Angenommen, die im vorherigen Beispiel erstellte Tabelle wird in der Datenbank als pvt gespeichert, und Sie möchten die Spaltenkennungen Emp1, Emp2, Emp3, Emp4 und Emp5 in Zeilenwerte, die einem bestimmten Anbieter entsprechen. Daher müssen Sie zwei zusätzliche Spalten identifizieren.Die Spalte, die die Spaltenwerte enthält, die Sie „drehen“ (Emp1, Emp2, …), heißt Employee und die Spalte, die die Werte enthält, die derzeit unter den zu drehenden Spalten vorhanden sind, heißt Orders. Diese Spalten entsprechen der Spalte pivot_column und value_column In der Transact-SQL-Definition. Hier ist die Abfrage.

Hier ist eine Teilergebnismenge.

Beachten Sie, dass UNPIVOT ist nicht die genaue Umkehrung von PIVOT. PIVOT führt eine Aggregation durch und führt mögliche mehrere Zeilen in der Ausgabe zu einer einzigen Zeile zusammen. UNPIVOT reproduziert das ursprüngliche Ausdrucksergebnis mit Tabellenwert nicht, da Zeilen zusammengeführt wurden. Außerdem verschwinden Nullwerte in der Eingabe von UNPIVOT Wenn die Werte verschwinden, wird angezeigt, dass möglicherweise vor der Operation PIVOT ursprüngliche Nullwerte in der Eingabe vorhanden waren.

Die Sales.vSalesPersonSalesByFiscalYears in der AdventureWorks2012-Beispieldatenbank verwendet PIVOT, um den Gesamtumsatz für jeden Verkäufer für jedes Geschäftsjahr zurückzugeben. Um die Ansicht in SQL Server Management Studio zu skripten Suchen Sie im Objekt-Explorer die Ansicht im Ordner „Ansichten“ für die AdventureWorks2012-Datenbank. Klicken Sie mit der rechten Maustaste auf den Namen der Ansicht, und wählen Sie dann „Skriptansicht“ aus.

Siehe auch

FROM (Transact -SQL)
CASE (Transact-SQL)