Spalten in Zeilen umwandeln

Unpivoting ist das Gegenstück zum Pivotieren: Sie nehmen eine breite Tabelle und wandeln ihre Spalten zurück in Zeilen um. Interviewerinnen und Interviewer fragen danach, wenn Daten im Tabellenkalkulationsformat vorliegen, für die Analyse aber normalisiert werden müssen.

Beispiel: Eine Tabelle mit den Spalten q1, q2, q3, q4 pro Region muss in Zeilen der Form (region, quarter, amount) umgewandelt werden. Diese lange Form eignet sich besser für Aggregationen, Joins und Diagramme.

Die dialektunabhängige Antwort lautet UNION ALL: Schreiben Sie eine SELECT-Abfrage pro Quellspalte, die jeweils ein festes Label und den Wert dieser Spalte ausgibt.

Verwenden Sie UNION ALL und nicht UNION, damit keine Kosten für die Duplikatentfernung entstehen und jede Zeile erhalten bleibt, auch wenn zwei Zellen denselben Wert enthalten.

Das ist eine klassische Falle im Vorstellungsgespräch. UNION entfernt doppelte Zeilen im gesamten Ergebnis. Wenn East und West für Q1 beide 100 enthielten, würde ein einfaches UNION identische Zeilen zusammenfassen, und Sie würden Daten verlieren.

UNION ALL hängt die Ergebnisse ohne Duplikatentfernung aneinander, genau wie es beim Unpivoting erforderlich ist. Außerdem ist es schneller, weil weder eine Sortierung noch ein Hashing zur Duplikatentfernung erforderlich ist.

Jeder Zweig von UNION ALL muss dieselbe Anzahl von Spalten mit kompatiblen Datentypen in derselben Reihenfolge liefern. Die Spaltennamen stammen aus der ersten SELECT-Abfrage.

Wenn sich die Typen Ihrer breiten Spalten unterscheiden (beispielsweise int und decimal), wählt die Engine einen gemeinsamen Typ. Sind die Typen tatsächlich inkompatibel, wandeln Sie sie explizit um, damit die Union nicht fehlschlägt.

SQL Server verfügt über einen eigenen UNPIVOT-Operator, der kompakter ist als UNION ALL. Sie benennen die neue Wertespalte und die neue Labelspalte und listen die Quellspalten auf, die zusammengeführt werden sollen.

Ein wichtiges Verhalten: UNPIVOT entfernt Zeilen, deren Wert NULL ist. In Vorstellungsgesprächen wird getestet, ob Sie diesen Nebeneffekt kennen.

Wenn eine Region in q3 den Wert NULL enthält, lässt SQL Servers UNPIVOT diese Zeile im Ergebnis einfach weg. Wenn Sie unabhängig von NULL-Werten eine Zeile für jede Spalte benötigen, verwenden Sie stattdessen UNION ALL, das diese Zeilen erhält.

Benennen Sie diesen Unterschied im Vorstellungsgespräch: Der native UNPIVOT-Operator ist kompakt, verliert aber NULL-Werte; UNION ALL ist ausführlicher, aber vollständig.

PostgreSQL hat kein UNPIVOT. Ein übersichtliches Idiom ist jedoch ein CROSS JOIN LATERAL über eine VALUES-Liste. Jede breite Zeile wird mit einer kleinen Inline-Tabelle aus (Label, Wert)-Paaren erweitert.

Das ist übersichtlicher als ein langes UNION ALL und liest die Quelltabelle nur einmal.

Ein erwähnenswerter Performance-Aspekt: Das naive UNION ALL liest die breite Tabelle einmal pro Zweig ein (bei vier Quartalen also viermal). Die Form mit LATERAL VALUES und SQL Server UNPIVOT lesen die Quelle dagegen nur einmal.

Bei großen Tabellen ist das wichtig. Wenn Sie UNION ALL verwenden müssen, kann der Optimizer die Tabelle trotzdem mehrfach lesen. Erwähnen Sie daher LATERAL oder UNPIVOT als effizientere Möglichkeit.

Mit UNION ALL oder LATERAL behalten Sie Zeilen mit NULL-Werten. Wenn die Fragestellung nur befüllte Zellen verlangt, fügen Sie einen Filter hinzu. Damit ahmen Sie das Verhalten von SQL Server UNPIVOT nach, das solche Zeilen automatisch entfernt.

Ob NULL-Werte erhalten oder entfernt werden sollen, hängt von der konkreten Anforderung ab. Klären Sie dies daher vor dem Programmieren mit der interviewenden Person.

Eine häufige Folgefrage lautet: „Geben Sie aus der vierteljährlichen Tabelle im Breitformat den Gesamtumsatz pro Region über alle Quartale hinweg aus.“ Nach dem Unpivotieren ins Langformat ist die Aggregation trivial: eine einzige nach Region gruppierte SUM-Aggregation.

Das zeigt den eigentlichen Grund, warum Sie zuerst unpivotieren sollten. Vier separate Spalten zu summieren ist fehleranfällig, aber ein SUM(amount) GROUP BY region im Langformat lässt sich auf beliebig viele Quartale erweitern.

Erkennen Sie in einer Aufgabenstellung das Signal zum Unpivotieren:

• Die Eingabe enthält wiederholte Spalten, die eigentlich Werte darstellen (Monate, Jahre, Kennzahlen).
• Sie müssen über diese Werte hinweg aggregieren, Joins durchführen oder Diagramme erstellen.
• Sie möchten beim Import denormalisierte Tabellendaten aus einer Tabellenkalkulation normalisieren.

Das Langformat ist für die weitere Arbeit mit SQL fast immer die richtige Struktur. Daher ist das Unpivotieren häufig ein erster Schritt.

Bestätigen Sie, dass Sie die häufigste Falle beim Unpivotieren kennen.

Beim Unpivotieren werden Spalten in Zeilen umgewandelt:

• Portabel: eine SELECT-Abfrage pro Spalte, mit UNION ALL verbunden (niemals ein einfaches UNION).
• SQL Server: natives UNPIVOT, kompakt, entfernt jedoch NULL-Werte.
• Postgres: CROSS JOIN LATERAL (VALUES ...), ein einziger Scan.
• Richten Sie die Anzahl und die Datentypen der Spalten in allen Abfragezweigen aufeinander aus. Filtern Sie NULL-Werte, wenn die Aufgabenstellung dies verlangt.