Einführung
In meinem letzten Artikel „Konvertieren von durch Kommas getrennten Werten in Zeilen und umgekehrt in SQL Server“ habe ich darüber gesprochen, wie Sie durch Kommas getrennte (oder durch ein anderes Zeichen getrennte) Werte in einer einzelnen Spalte in Zeilen konvertieren können und umgekehrt. In diesem Artikel zeige ich, wie Sie Zeilenwerte in Spaltenwerte (PIVOT) und Spaltenwerte in Zeilenwerte (UNPIVOT) in SQL Server konvertieren können.,
Konvertieren von Zeilen in Spalten-PIVOT
SQL Server verfügt über einen relationalen PIVOT-Operator, um die eindeutigen Werte einer angegebenen Spalte aus mehreren Zeilen in mehrere Spaltenwerte in der Ausgabe umzuwandeln (Cross-tab), wodurch eine Tabelle effektiv gedreht wird. Es ermöglicht auch die Durchführung von Aggregationen, wo immer erforderlich, für Spaltenwerte, die in der endgültigen Ausgabe erwartet werden. Die grundlegende Syntax für einen PIVOT-relationalen Operator sieht folgendermaßen aus:
Mit dem folgenden Skript erstellen wir eine Tabelle und laden einige Daten hinein., Wie Sie im Bild unten sehen können, gibt es seit einigen Jahren Verkaufsinformationen für einige Länder. Wenn Sie bemerken, gibt es für jedes Land und für jedes Jahr eine separate Zeile.
Jetzt konvertieren wir mit dem PIVOT-Operator Zeilenwerte in Spaltenwerte mit dem unten angegebenen Skript und den Ergebnissen wie im Bild unten gezeigt.
Obwohl wir die Summenaggregationsfunktion verwendet haben, gibt es in diesem Fall keine Summierung, da es für jede eindeutige Kombination für Land und Jahr nur eine Zeile gibt., Bitte beachten Sie die Verwendung von Klammern für Pivot-Spaltenwerte; Diese sind erforderlich.
Dynamik in den PIVOT bringen
Wenn Sie im obigen Skript bemerken, haben wir Werte (2005, 2006, 2007 und 2008) für Pivot-Spalten bereitgestellt, da diese Werte in den ursprünglichen Datensätzen verfügbar sind. Was aber, wenn in Zukunft einige zusätzliche Werte erwartet werden, zum Beispiel 2009 und 2010 usw. für die pivot-Spalte?,
In diesem Fall können Sie weiterhin die Pivot-Spaltenwerte verwenden, von denen erwartet wird, dass sie in Zukunft kommen (oder die im ursprünglichen Datensatz noch nicht verfügbar sind), obwohl Sie NULL für ihre Werte sehen. Das folgende Skript zeigt dieses Szenario und das folgende Bild zeigt Nullwerte für die Jahre 2009 und 2010, da für diese Jahre keine Daten vorliegen.
Der oben diskutierte Ansatz funktioniert einwandfrei, wenn Sie bereits alle möglichen Werte für die Pivot-Spalte kennen.,
In diesem Fall können Sie eine dynamische Abfrage schreiben, um zuerst alle eindeutigen Werte für die Pivot-Spalte zur Laufzeit abzurufen, und dann eine dynamische Schreibabfrage, um sie mit der Pivot – Abfrage auszuführen, wie unten gezeigt:
Konvertieren von Spalten in Zeilen-UNPIVOT
UNPIVOT ist ein weiterer relationaler Operator in SQL Server, der fast den umgekehrten Vorgang von PIVOT durch Drehen von Spaltenwerten in Zeilenwerte ausführt. Lassen Sie mich dies anhand eines Beispiels demonstrieren; Lassen Sie uns mit dem folgenden Skript eine Tabelle mit schwenkbaren Daten aus der vorherigen Abfrage erstellen., Das Bild unten zeigt Daten der neu erstellten Tabelle mit schwenkbaren Daten.
Um Spaltenwerte in Zeilenwerte zu drehen, verwenden wir nun den UNPIVOT-Operator wie unten gezeigt. Das Bild unten zeigt gedrehte Daten:
Wenn Sie zurückgreifen, sagte ich, dass UNPIVOT fast das Gegenteil des PIVOT-Operators ist. Der Grund dafür ist, dass Pivot beim Drehen von Zeilenwerten zu Spaltenwerten eine Aggregation durchführt und möglicherweise mehrere Zeilenwerte zu einem einzelnen Spaltenwert in der Ausgabe zusammenführt., Betrachten wir zum Beispiel für ein bestimmtes Land und Jahr gibt es zwei Werte, sagen wir 5000 und 6000. Wenn Sie es jetzt schwenken, hat die Ausgabe 11000 als Spaltenwert, wenn Sie SUM als Aggregationsfunktion haben. Später, wenn Sie es wieder entpacken möchten, erhalten Sie 11000 nicht die bi-furcated Werte (5000 und 6000 als Original). Trotzdem können wir sagen, wenn die Pivot-Werte aggregierte Werte sind, ist es nicht möglich, die ursprünglichen Daten zurückzubekommen.,
Fazit
In diesem Artikel habe ich gezeigt, wie Sie Zeilenwerte in Spaltenwerte (PIVOT) und Spaltenwerte in Zeilenwerte (UNPIVOT) in SQL Server konvertieren können. Ich habe auch darüber gesprochen, eine dynamische Abfrage zu schreiben, um eine dynamische Pivot-Abfrage zu schreiben, in der mögliche Pivot-Spaltenwerte zur Laufzeit bestimmt werden.