Die Ausgangstabelle

Die SELECT-Spalte

In diesem Beispiel wird die Spalte Produkt als SELECT-Spalte verwendet. Durch die Gruppierung für diese Spalte wird jeder unterschiedliche Wert einmal in einer Zeile aufgeführt.

Die PIVOT-Spalte

Die Spalte Verkäufer wird als Pivot-Spalte verwendet. Für jeden Wert (ohne Duplikate) wird eine Spalte erzeugt. Um dies zu erreichen, muss für jeden eindeutigen Wert eine entsprechende CASE-Anweisung erzeugt werden

Die AGGREGAT-Spalte

Eine numerische Spalte wird verwendet um die Aggregatberechnung durchzuführen. Im Beispiel wird die Summen-Funktion verwendet, aber auch andere Funktionen sind denkbar:

Die AGGREGAT-Funktion

Neben der Summe, können auch der Durchschnit (AVG), das Maximum bzw. Minimum (MAX/MIN) oder die Standardabweichung berechnet werden (STDEV). Um die Prozedur möglichst flexibel zu halten, wird deshalb die zu verwendende Funktion ebenfalls als Parameter übergeben

Eine allgemeine "Kreuztabellen-Prozedur"

Wie kann nun eine allgemeine Kreuztabellen-Prozedur aussehen?

Ein kurzer Blick auf das vorige Beispiel, dieses Mal  mit einigen farblichen Hervorhebungen zeigt die Richtung:

SELECT
    Produkt ,
    SUM(CASE [Verkäufer] WHEN 'Maier' Then Umsatz ELSE 0 END) As 'Maier',
    SUM(CASE [Verkäufer] WHEN 'Müller' Then Umsatz ELSE 0 END) As 'Müller',
    SUM(Umsatz) As Summe
FROM [Verkäufe] GROUP BY Produkt 
WITH CUBE

Die meisten Teile dieser Anweisung können recht einfach zusammengesetzt werden. Der Mittelteil - also die Erzeugung der CASE-Anweisungen - gestaltet sich jedoch etwas schwieriger: Zunächst müssen alle eindeutigen Werte der Pivot-Spalte ermittelt werden und anschließend zu entsprechenden CASE-Anweisungen zusammengesetzt werden.

Was auf den ersten Blick recht kompliziert erscheint, lässt sich mit Transact-SQL recht leicht umsetzen:

Erster Versuch: Ermitteln aller eindeutigen Werte aus der Pivot-Spalte

SELECT DISTINCT [Verkäufer] FROM [Verkäufe]

Ergebnis

verkäufer 
-------------------- 
Maier
Müller
Schulze

Zweiter Versuch: Erstellen einer komma-getrennten Liste aller Verkäufer

DECLARE @Liste varchar(4000)
SET @Liste = ''
SELECT @Liste = @Liste + [Verkäufer] + ',' FROM (SELECT DISTINCT [Verkäufer] FROM [Verkäufe]) V
SELECT @Liste 

Ergebnis:

Maier,Müller,Schulze,

Jetzt ist es nur noch ein kleiner Schritt, bis zur kompletten Erzeugung der CASE-Anweisungen:

DECLARE @Liste varchar(4000)
SET @Liste = ''
SELECT @Liste = 
               @Liste + 
              'SUM (CASE [Verkäufer] WHEN ''' + 
              [Verkäufer] + 
              'THEN Umsatz ELSE 0 END, ' 
              FROM (SELECT DISTINCT [Verkäufer] FROM [Verkäufe]) V
SELECT @Liste 

Das Ergebnis:

SUM (CASE [Verkäufer] WHEN 'Maier' THEN Umsatz ELSE 0 END, 
SUM (CASE [Verkäufer] WHEN 'Müller' THEN Umsatz ELSE 0 END, 
SUM (CASE [Verkäufer] WHEN 'Schulze' THEN Umsatz ELSE 0 END, 

Das Finale

Nachdem die erste Hürde erfolgreich überwunden ist, bleibt noch das Zusammensetzen der gesamten SQL-Anweisung.

Um alles flexibel zu halten, werden die einzelnen Komponenten der Anweisung gleich als Parameter definiert:

DECLARE @Aggregat_Funktion nvarchar(50)
DECLARE @Aggregat_Spalte nvarchar(255)
DECLARE @Ausgangstabelle nvarchar(255)
DECLARE @Select_Spalte nvarchar(255)
DECLARE @Pivot_Spalte nvarchar(255)

Zusätzlich werden noch einige Variablen für die eigentliche SELECT-Anweisung benötigt. In der Variablen @SQL_Anfang soll der einleitende Teil der SELECT-Anweisung abgelegt werden. Die Variable @SQL_Liste wird verwendet, um die Liste der CASE-Anweisungen dynamisch abzufragen. Das Ergebnis wird in @SQL_Mitte abgelegt.

In der Variablen @SQL_Ende werden schließlich alle verbleibenden Anweisungen (Summenbildung, Gruppierung, CUBE) untergebracht.

DECLARE @SQL_Anfang nvarchar(2000)
DECLARE @SQL_Liste nvarchar(4000)
DECLARE @SQL_Mitte nvarchar(4000)
DECLARE @SQL_Ende nvarchar(2000)

Also ans Werk: Zunächst werden die Parameter für unser Beispiel vorbelegt:
SET @Aggregat_Funktion = 'SUM'
SET @Aggregat_Spalte = 'Umsatz'
SET @Ausgangstabelle = '[Verkäufe]'
SET @Select_Spalte = 'Produkt'
SET @Pivot_Spalte = '[Verkäufer]'

Anschließend wird der erste Teil der SELECT-Anweisung dynamisch erzeugt:

(So soll es im Beispiel aussehen: SELECT Produkt, )

SET @SQL_Anfang = 'SELECT ' + @Select_Spalte + ', '

Jetzt kommt der schwierige Part: Wie schon gesehen, kann die Liste der CASE-Anweisungen dynamisch erzeugt werden. Jetzt stellt sich aber als zusätzliches Problem, daß die Spaltennamen ebenfalls dynamisch übergeben werden. Abhilfe kann hier die Prozedur sp_executesql schaffen. Mit dieser Prozedur kann eine dynamisch erzeugte SQL-Anweisung ausgeführt werden. Was für diesen Fall jedoch noch wichtiger ist, ist die Möglichkeit einen Parameter aus der dynamischen erzeugten Anweisung wieder nach aussen zurückzugeben.
Zuerst wird also die eigentliche Anweisung dynamisch erzeugt. 

SET @SQL_Liste = 'SELECT @Liste = ' +
' @Liste + ''' +
@Aggregat_Funktion + ' (CASE ' +
@Pivot_Spalte + ' WHEN '''''' + ' + 
@Pivot_Spalte + 
' + '''''' THEN ' +
@Aggregat_Spalte + ' ELSE 0 END) AS ' +
@Pivot_Spalte + ', ''' +
' FROM (SELECT DISTINCT ' + @Pivot_Spalte + 
' FROM ' + @Ausgangstabelle + ') V ' 
Anschließend kann diese Anweisung ausgeführt werden. Die Variable @Liste wird von außen übergeben und auch wieder ausgelesen:

SET @SQL_Mitte = ''
EXEC sp_executesql @sql_liste, N'@Liste nvarchar(4000) OUTPUT', @Liste = @SQL_Mitte OUTPUT

Verbleibt noch das Zusammenbauen der restlichen Anweisungen (Summenbildung für die SELECT-Spalte, Gruppieren nach der SELECT-Spalte und CUBE bilden:

(So soll es aussehen: SUM(Umsatz) As Summe FROM [Verkäufe] GROUP BY Produkt WITH CUBE)

SET @SQL_Ende = @Aggregat_Funktion + '(' + 
@Aggregat_Spalte + ') As Summe ' + 
' FROM ' + @Ausgangstabelle + 
' GROUP BY ' + @Select_Spalte + 
' WITH CUBE'

Geschafft! Als letztes werden die drei Anweisungen (Start, Mitte, Ende) zusammengesetzt und ausgeführt:
EXEC (@SQL_Anfang + @SQL_Mitte + @SQL_Ende)

Falls Sie das ganze selbst ausprobieren möchten, laden Sie doch einfach das fertige Script von unserem Server.

War's das?

Wie fast immer im wirklichen Leben gilt es bei realen Problemstellungen noch einige weitere Besonderheiten zu beachten.

In dem gezeigten kleinen Beispiel waren einige Randbedingungen erfüllt, die die Ausführung vereinfacht haben.

So tauchten zunächst in der Aggregat-Spalte keine NULL-Werte auf. Dadurch ist immer ein korrektes Resultat gewährleistet. Falls jedoch Null-Werte auftreten können, müssen diese gesondert behandelt werden. Bei den Werten in der Pivot-Spalte handelte es sich außerdem um Zeichenketten. Testen Sie die kleine Prozedur einmal mit einer numerischen Spalte (z.B. eine ID oder eine Jahreszahl). Der Vergleich in der CASE-Anweisung wird scheitern, da die Datentypen für den Vergleich nicht übereinstimmen.

Die folgende gespeicherte Prozedur sp_Transform versucht diese Eventualitäten weitestgehend abzufangen. Testen Sie die Prozedur doch einmal. Falls Sie auf Probleme stossen, freuen wird uns über ein Feedback. (Natürlich auch, wenn Ihnen die Prozedur weiterhilft.)

Wie geht es weiter?

Die gezeigte Prozedur hilft sicherlich einige kleinere Problemstellungen relativ schnell zu lösen. Sollen die Gruppierungen jedoch auf mehreren Stufen erfolgen, sollten Sie auf jeden Fall einen Blick auf Microsofts OLAP-Server werfen. Der OLAP-Server wird zusammen mit dem SQL Server ausgeliefert. Im Gegensatz zu einer dynamischen Abfrage (wie mit sp_Transform) werden hier die Daten jedoch zunächst aufbereitet. Für unseren Fall würde das heißen, das zunächst die Dimensionen aufbereitet werden (Alle DISTINCT Werte aus Produkte und Verkäufer). Anschließend werden aus der eigentlichen Faktentabelle alle beötigten Zwischensummen berechnet und ebenfalls abgespeichert. Die Abfrage darüber ist natürlich wesentlich schneller, da kaum noch Berechnungen ausgeführt werden müssen und keine gleichzeitigen Aktualisierungen an den Daten vorgenommen werden.