Crosstab & Tabellen
Crosstab oder Pivotisieren von Tabellen
Manchmal ist es notwendig eine Kreuz- oder Pivottabelle mit Hilfe von SQL zu generieren. Warum? Zum Beispiel
- Sie nutzen eine Reporting-Anwendung, die diese Funktionalität nicht unterstützt.
- Sie nutzen eine historisch gewachsene Anwendung, die Sie nicht anpassen können.
- Sie wollen bereits formatierte Daten ausgeben, z.B. in eine Textdatei.
Lassen Sie uns eine typische Situation betrachten, in dem Sie eine Pivotabelle erzeugen wollen. Als Basis dient uns eine View BEST_BUYER, die 3 umsatzstärksten Kunden pro Monat anzeigt:
-
CUST ORDER_MO ORDER_SUM RANK ---------- -------- --------- ----- Young OCT-2008 20.50 1 Black MAY-2009 21.17 1 Jones OCT-2009 76.40 1 Adams OCT-2009 63.21 2 Brown OCT-2009 42.62 3 Green NOV-2009 43.61 1 Adams NOV-2009 40.05 2 Jones NOV-2009 31.89 3
Allerdings ist es ein wenig schwierig, die Daten zu lesen und zu vergleichen, oder ein Diagramm zu erstellen, so dass Sie an diesem Punkt über eine Pivottabelle nachdenken, in der Monate Titel jeder Zeilen darstellen. Kundennamen werden in die Spalten "1", "2" und "3"anhand des Umsatzes des jeweiligen Monats platziert.
-
SELECT order_month , MAX(CASE WHEN cust_rank = 1 THEN cust ELSE NULL END) "1" , MAX(CASE WHEN cust_rank = 2 THEN cust ELSE NULL END) "2" , MAX(CASE WHEN cust_rank = 3 THEN cust ELSE NULL END) "3" FROM best_buyer GROUP BY order_month ;
-
ORDER_MO 1 2 3 -------- ---------- ---------- ---------- OCT-2009 Jones Adams Brown OCT-2008 Young NOV-2009 Green Adams Jones MAY-2009 Black
Crosstab mit Hilfe eines Skripts erstellen
Unsere Query liefert bereits das erwünschte Ergebnis. Allerdings erfordert diese Art von Queries viel Schreibarbeit, insbesondere wenn mehr als 3 Spalten im Spiel sind: man muss alle Werte kennen und sie einzeln für die Spaltenberechnung und -benennung einfügen.
Hier kann Skripting eine Abhilfe schaffen. Im folgenden werden wir ein Skript schreiben, dass eine beliebige Tabelle oder View anhand bestimmter Einstellungen erstellt.
Im ersten Schritt erstellen wir einen Skriptrumpf:
-
- CREATE OR REPLACE SCRIPT "GET_PIVOT"(objectname,col_select,col_pivot,col_aggregate,aggregate_function) RETURNS TABLE AS
- /*
- objectname: a selectable table or view
- col_select: column for first column in result table
- for this column row values are displayed
- col_pivot: column that is transformed into columns
- for this column column values are displayed
- col_aggregate: name of column or an appropriate expression for aggregate
- aggregate_function: the aggregate function to use for the pivot
- */
- return
- /
Zunächst holen wir uns Werte für die Spaltennamen unserer Pivot-Tabelle. Diese Werte entnehmen wir der Spalte COL_PIVOT der Originaltabelle OBJECTNAME:
-
- -- get values will be used as column caption
- local collist = query([[select distinct ::col_p as val from ::obj order by ::col_p]], {obj = objectname, col_p = col_pivot} )
Nun haben wir alle nötigen Werte, die wir in der Selectliste verwenden sollen in einem Dictionary COLLIST. Jetzt können wir die endgültige Query zusammensetzen. Wir benötigen zusätzlich noch einen Spaltennamen (COL_SELECT) für die erste Spalte der Pivottabelle: in unserem Beispiel handelt es sich hierbei um einen Bestellmonat.
-
- local querytext = [[select ::col_s]]
- -- iterate through column captions
- for i=1, #collist do
- querytext = querytext..
- -- aggregate values of the col_aggregate column if the value the corresponding col_pivot contains the current caption
- [[, ]]..aggregate_function..
- [[(CASE WHEN ::col_p = ']]..collist[i].VAL..
- [[' THEN ::col_a ELSE NULL END) AS ]]
- -- use current caption as an alias, quote it to preserve case sensitivity
- ..quote(collist[i].VAL)
- end
- querytext = querytext..[[ from ::obj
- group by ::col_s order by ::col_s]]
Wir nutzen hier noch 2 Parameter: eine AGGREGATE_FUNCTION für maximale Flexibilität bei der Berechnung von Pivot-Werten und einen Spaltennamen COL_A, die diese Werte enthält. Dieser Parameter kann auch ein Ausdruck sein.
Nun können wir unsere zusammengesetzte Query aufrufen und das Ergebnis zurückgeben. Bitte beachten Sie, das Ergebnis des Skripts kann nicht in weiteren Anfragen verarbeitet werden.
-
- return query(querytext, {obj = objectname, col_s = col_select, col_p = col_pivot, col_a = col_aggregate})
Das komplette Skript können Sie hier herunterladen. Um das mit Hilfe von plain SQL erzieltes Ergebnis zu erreichen, reicht es aus, das Skript mit folgenden Parametern aufzurufen:
-
- EXECUTE SCRIPT get_pivot('BEST_BUYER', 'ORDER_MONTH', 'CUST_RANK', 'CUST', 'MAX');
Da das Skript vollständig parametrisiert ist, können wir es verwenden, um den monatlichen Umsatz unserer besten Kunden zu errechnen:
-
- EXECUTE SCRIPT get_pivot('BEST_BUYER', 'ORDER_MONTH', 'CUST', 'ORDER_SUM', 'SUM');
-
- ORDER_MO Adams Black Brown Green Jones Young
- -------- -------- -------- -------- -------- -------- --------
- MAY-2009 21.17
- NOV-2009 40.05 43.61 31.89
- OCT-2008 20.50
- OCT-2009 63.21 42.62 76.40
