PIVOT- und Kreuztabellensyntax der Anbieter

Sie kennen bereits den portablen CASE-Pivot. Interviewerinnen und Interviewer möchten aber auch wissen, ob Sie anbieterspezifische Pivot-Operatoren verwenden können, wenn diese verfügbar sind.

SQL Server bietet einen eigenen PIVOT-Operator. PostgreSQL stellt in der Erweiterung tablefunc die Funktion crosstab bereit. Wenn Sie beide Möglichkeiten und ihre Tücken kennen, zeigt das praktische Erfahrung.

SQL Servers PIVOT benötigt drei Dinge:

• Ein Aggregat über der Wertespalte.
• Eine FOR-Klausel, die die Spalte benennt, deren Werte zu neuen Spalten werden.
• Eine IN-Liste der Literalwerte, die in Spalten umgewandelt werden sollen.

Der Operator muss auf eine abgeleitete Tabelle angewendet werden, die genau den Schlüssel, die Spalte zum Aufspalten und den Wert enthält, und keine weiteren Spalten.

Eine subtile PIVOT-Tücke, die in Vorstellungsgesprächen gern getestet wird: Die Gruppierung erfolgt implizit. SQL Server gruppiert nach jeder Spalte der Quelle, die weder die aggregierte Spalte noch die FOR-Spalte ist.

Wenn Ihre abgeleitete Tabelle also versehentlich eine zusätzliche Spalte wie order_id enthält, gruppiert der Pivot auch danach, und Sie erhalten deutlich mehr Zeilen als erwartet. Beschränken Sie die innere Abfrage immer auf Schlüssel, Aufspaltspalte und Wert.

In SQL Server sind die Namen der pivotierten Spalten die Literalwerte aus den Daten, eingeschlossen in eckige Klammern. Wenn ein Wert mit einer Ziffer beginnt oder Leerzeichen enthält, sind die Klammern zwingend erforderlich.

Sie wählen diese Spalten in der äußeren SELECT-Abfrage mit demselben Namen in eckigen Klammern aus. Deshalb kann PIVOT auch keine unbekannten Werte ohne dynamisches SQL verarbeiten: Die IN-Liste ist fest im Code vorgegeben.

PostgreSQL hat kein Schlüsselwort PIVOT. Stattdessen stellt die Erweiterung tablefunc die Funktion crosstab bereit, die eine SQL-Zeichenkette entgegennimmt und deren Ergebnis umstrukturiert.

Sie müssen die Erweiterung zunächst aktivieren. crosstab erwartet, dass die Quellabfrage genau drei Spalten zurückgibt: Zeilenbezeichner, Kategorie und Wert, und zwar in dieser Reihenfolge.

Der fehleranfälligste Teil von crosstab ist die nachgestellte Spaltendefinitionsliste AS ct(...). Sie müssen die Namen und Datentypen der Ausgabespalten selbst deklarieren. Diese müssen mit der Anzahl und Reihenfolge der Kategorien übereinstimmen.

Wenn eine Kategorie für eine Zeile fehlt, füllt crosstab die Spalten positionsbezogen. Dadurch können sich Daten verschieben, sofern Sie nicht die unten beschriebene Zwei-Argument-Form verwenden.

Um eine falsche Zuordnung zu vermeiden, wenn einigen Zeilen bestimmte Kategorien fehlen, verwenden Sie die Zwei-Argument-Form. Die zweite Abfrage gibt die vollständige, geordnete Liste der Kategorienwerte zurück. Dadurch weiß crosstab genau, in welche Spalte jeder Wert gehört.

Diese robuste Form erwarten Interviewerinnen und Interviewer, wenn Kategorien nur für einige Zeilen vorhanden sind.

Wenn die interviewende Person nach MySQL fragt, lautet die direkte Antwort: MySQL hat weder PIVOT noch crosstab. Ihre einzige Möglichkeit ist dort die bedingte Aggregation mit CASE (oder die Kurzform SUM(... ) + IF()).

Genau deshalb ist das portable CASE-Muster so wertvoll: Es ist der kleinste gemeinsame Nenner, der überall funktioniert.

Eine typische Anforderung für einen Bericht lautet: "eine Zeile pro Region mit einer Spalte, die die Bestellungen je Status zählt". In SQL Server übergeben Sie eine gekürzte abgeleitete Tabelle an PIVOT und verwenden COUNT.

Da Sie die Statusspalte selbst zählen, wird jede Zeile mit einem Wert ungleich NULL in einem Bucket mitgezählt. Die äußere SELECT-Abfrage führt jeden Status als Spalte in eckigen Klammern auf. Das ist die kompakte Alternative zu drei Ausdrücken der Form COUNT(CASE ...).

PIVOT und crosstab haben dieselbe grundlegende Einschränkung wie die bedingte Aggregation: Die Ausgabespalten müssen beim Schreiben der Abfrage bekannt sein.

• SQL Server: Die IN-Liste enthält Literale.
• Postgres crosstab: Die Spaltendefinitionsliste enthält Literale.

Keine der beiden Möglichkeiten kann Kategorien zur Laufzeit ermitteln. Dafür muss die SQL-Zeichenkette dynamisch erstellt werden.

Eine gute Antwort im Vorstellungsgespräch vergleicht die Möglichkeiten ehrlich:

• CASE-Aggregation: portabel, gut lesbar und mit jeder Engine kompatibel. Die Standardwahl.
• SQL Server PIVOT: bei vielen Spalten kompakt, aber die implizite Gruppierung sorgt häufig für Überraschungen.
• Postgres crosstab: leistungsfähig, aber ausführlich; benötigt eine Erweiterung und eine Spaltendefinitionsliste.

Wenn Sie unsicher sind, greifen Sie zur bedingten Aggregation und erwähnen Sie die anbieterspezifischen Operatoren als Alternativen.

Prüfen Sie, welches Verhalten von SQL Server PIVOT in Vorstellungsgesprächen besonders häufig hinterfragt wird.

Die Pivot-Syntax der verschiedenen Anbieter auf einen Blick:

• SQL Server: PIVOT (SUM(x) FOR col IN ([a],[b])) mit einem impliziten GROUP BY über die verbleibenden Spalten.
• Postgres: crosstab() aus tablefunc, benötigt eine Spaltendefinitionsliste; verwenden Sie bei Daten mit fehlenden Kategorien die Zwei-Argument-Form.
• MySQL: Keine der beiden Möglichkeiten ist vorhanden, verwenden Sie CASE.
• Bei allen drei Möglichkeiten müssen die Spalten beim Schreiben der Abfrage bekannt sein.