Nicht immer sind die Spalten fest definiert, nach denen man eine Tabelle pivotieren möchte. Falls die Spalten auch erst nach Anwendung anderer Kriterien feststehen, hilft nur noch dynamisches SQL um das passende Statement zur Laufzeit zu erzeugen.

Die Fragestellung

Es gibt eine Tabelle (Artikel) mit Material aus verschiedenen Werkstoffen in verschiedenen Größen, welches von verschiedenen Herstellern bezogen wird. Ebenfalls in der Tabelle ist die aktuelle Lagermenge (in Stück) verzeichnet. Bei der Abfrage der Artikel sollen nur gewisse Hersteller berücksichtigt werden. Als Ergebnis soll eine Matrix ausgegeben werden, die zu jedem Werkstoff die Lagermenge für die verschiedenen Größen angibt. Die Matrix soll in der vertikalen Richtung nach Werkstoff sortiert sein und in der horizontalen Ausrichtung nach den Größen.

Hier sehen wir die Einschränkung auf die Hersteller als Ursache für die nicht bekannten Spalten. Die Größen der Materialien für die Matrix können erst dann bestimmt werden, wenn die Artikel über die Hersteller gefiltert werden.

Lösung für die Spaltenliste

Zuerst einmal müssen wir die eindeutigen Kombinationen von Werkstoff und Abmessung ermitteln. Diese werden ja wie oben beschrieben über die Hersteller gefiltert. Die Abmessung benötigen wir später als varchar Wert. Daher ist dann keine Sortierung über die numerischen Werte mehr möglich. "16" soll natürlich nicht kleiner als "2" sein. Daher multiplizieren wir die Abmessungen mit 10, um die eine mögliche Nachkommastelle zu eliminieren. Diesen Integer-Wert behalten wir zusätzlich zur späteren Sortierung bei. Wenn man es genau nimmt, hätte man auch den REAL-Wert weiterverwenden können, Aber distinkte Werte sind mir hier einfach lieber.

Frank Kalis hat in seinem Artikel Spaltenwerte als kommaseparierte Liste zurückgeben Teil 2 beschrieben, wie man sich eine kommaseparierte Liste erzeugt. Daher verzichte ich hier auf die Details. Ich habe lediglich das Statement durch die Ersetzung von LEFT/LEN durch STUFF etwas übersichtlicher gestaltet.

Damit ich die distinkten Spalten nach der neu erstellten Spalte "Sortierung" sortieren kann, obwohl ich diese nicht im Resultset verwenden möchte, benötige ich eine derived table. Die Hintergründe dazu sind im Märchen von ORDER BY beschrieben.

Wir definieren uns also eine lokale Variable, in der wir die Spaltenliste aufbereiten. Die Abmessungen sollen noch ein "M" davor bekommen. Alle Spalten müssen mit eckigen Klammern umgeben sein, damit wir diese später problemlos im PIVOT-Statement verwenden können.

Dynamisches Pivot

Der Rest ist dann nicht mehr so schwer. Wir müssen lediglich ein syntaktisch korrektes PIVOT-Statement in einer lokalen Variablen aufbereiten. Die Spaltenliste haben wir ja oben schon erledigt. Hier verwenden wir wieder eine Common Table Expression (CTE) mit dem Namen FilteredData, welche uns aus der Tabelle Artikel die entsprechenden Sätze mit Lagermenge für die gewünschten Hersteller liefert. Diese CTE ist dann der Input für das PIVOT-Statement.

Die aufbereiteten Spalten benötigen wir zwei mal. Einmal in der Select-Liste, damit die Tabelle auch wirklich so aussieht, wie gewünscht, zum anderen in der IN-Bedingung des PIVOT-Teils.

Ganz am Ende sortieren wir noch das Ergebnis nach dem Werkstoff, damit dieser auch in alphabetischer Reihenfolge ausgegeben wird.

Das so aufbereitete Statement führen wir am Ende mit EXEC aus.

Das Ergebnis

In diesem Bild sehen wir also die Spalten und Werkstoffe in der gewünschten Reihenfolge. Das Element M3 ist zwei mal im Lager (zwei Hersteller) und M4 (ROSTFREI A2) ist drei mal vorhanden, da es von drei Herstellern geliefert wurde. Der einzige Satz für M8 kommt vom letzten Lieferanten.

  pivot_order_by.sql