Einfacher Weg, Spalten und Zeilen in SQL zu transponieren?
Wie kann ich in SQL einfach Spalten mit Zeilen vertauschen? Gibt es einen einfachen Befehl zum Transponieren?
D.h. dieses Ergebnis umdrehen:
Paul | John | Tim | Eric
Red 1 5 1 3
Green 8 4 3 5
Blue 2 2 9 1
in dies:
Red | Green | Blue
Paul 1 8 2
John 5 4 2
Tim 1 3 9
Eric 3 5 1
PIVOT
scheint für dieses Szenario zu komplex.
100
3
Es gibt mehrere Möglichkeiten, wie Sie diese Daten umwandeln können. In Ihrem ursprünglichen Beitrag haben Sie angegeben, dass "PIVOT" für dieses Szenario zu komplex zu sein scheint, aber es kann sehr einfach mit den Funktionen [UNPIVOT] und PIVOT in SQL Server angewendet werden.
Wenn Sie jedoch keinen Zugriff auf diese Funktionen haben, kann dies mit
UNION ALL
aufUNPIVOT
und dann einer Aggregatfunktion mit einerCASE
-Anweisung aufPIVOT
repliziert werden:Tabelle erstellen:
Union All, Aggregate und CASE Version:
Siehe SQL Fiddle mit Demo
Die Anweisung
UNION ALL
führt denUNPIVOT
der Daten durch, indem die SpaltenPaul, John, Tim, Eric
in separate Zeilen umgewandelt werden. Dann wenden Sie die Aggregatfunktionsum()
mit der Anweisungcase
an, um die neuen Spalten für jedeFarbe
zu erhalten.Unpivot und Pivot Statische Version:
Die Funktionen "UNPIVOT" und "PIVOT" in SQL Server machen diese Umwandlung viel einfacher. Wenn Sie alle Werte kennen, die Sie umwandeln möchten, können Sie sie in eine statische Version fest einprogrammieren, um das Ergebnis zu erhalten:
Siehe SQL Fiddle mit Demo
Die innere Abfrage mit
UNPIVOT
führt die gleiche Funktion aus wieUNION ALL
. Sie nimmt die Liste der Spalten und verwandelt sie in Zeilen, derPIVOT
führt dann die endgültige Umwandlung in Spalten durch.Dynamische Pivot-Version:
Wenn Sie eine unbekannte Anzahl von Spalten (in Ihrem Beispiel "Paul, John, Tim, Eric") und eine unbekannte Anzahl von Farben haben, die umgewandelt werden sollen, können Sie dynamisches Sql verwenden, um die Liste für "UNPIVOT" und dann für "PIVOT" zu erstellen:
Siehe SQL Fiddle mit Demo
Die dynamische Version fragt sowohl
yourtable
als auch die Tabellesys.columns
ab, um die Liste der Elemente fürUNPIVOT
undPIVOT
zu erstellen. Diese wird dann zu einem Abfrage-String hinzugefügt, der ausgeführt werden soll. Der Vorteil der dynamischen Version besteht darin, dass bei einer sich ändernden Liste von "Farben" und/oder "Namen" die Liste zur Laufzeit erstellt wird.Alle drei Abfragen liefern das gleiche Ergebnis:
Dies erfordert normalerweise, dass Sie vorher ALLE Spalten- UND Zeilenbezeichnungen kennen. Wie Sie in der nachstehenden Abfrage sehen können, werden die Bezeichnungen sowohl bei der UNPIVOT- als auch bei der (erneuten) PIVOT-Operation vollständig aufgeführt.
MS SQL Server 2012 Schema einrichten:
Abfrage 1:
Ergebnisse:
Zusätzliche Hinweise:
Basierend auf dieser Lösung von bluefeet ist hier eine gespeicherte Prozedur, die dynamisches Sql verwendet, um die transponierte Tabelle zu erzeugen. Sie setzt voraus, dass alle Felder numerisch sind, mit Ausnahme der transponierten Spalte (der Spalte, die in der resultierenden Tabelle die Überschrift sein wird):
Sie können es mit der Tabelle testen, die mit diesem Befehl bereitgestellt wird: