Wie pivotiert man Ergebnisse einer SQL-Abfrage

SQL ist ein mächtiges Werkzeug zur Verarbeitung von Datenmengen. Aggregierungen spielen zur Auswertung meist ein große Rolle. Vor einiger Zeit haben wir bereits über das GROUP BY geschrieben, heute geht es um die Pivotierung. Dabei geht es darum, die zeilenweise organisierten Daten in Spalten zu transformieren. Nehmen wir folgendes Beispiel anhand der Daten des SH-Schemas der von Oracle bereitgestellten Sample-Schemas. Es sollen die Summen der Verkäufe in allen Produktkategorien pro Jahr ermittelt werden.

SQL> li
  1  select t.CALENDAR_YEAR, p.PROD_CATEGORY, sum(QUANTITY_SOLD), sum(s.AMOUNT_SOLD)
  2  from sales s
  3  join products p on (s.prod_id = p.prod_id)
  4  join times t on (s.time_id = t.time_id)
  5  group by  t.CALENDAR_YEAR, p.PROD_CATEGORY
  6  ;

CALENDAR_YEAR PROD_CATEGORY                                      SUM(QUANTITY_SOLD) SUM(S.AMOUNT_SOLD)
------------- -------------------------------------------------- ------------------ ------------------
         1999 Electronics                                                     24105         2916369,92
         2001 Photo                                                           34787         6333628,91
         2000 Peripherals and Accessories                                     58851         6464786,22
         1999 Hardware                                                         2809         3781243,27
         1998 Photo                                                            9283         3414323,88
         1998 Software/Other                                                  85233         2975108,91
         1999 Peripherals and Accessories                                     86605         7766237,11
         2000 Photo                                                           28045         4329248,89
         2000 Software/Other                                                 106501         3434409,23
         2000 Electronics                                                     36161         5200605,88
         2001 Electronics                                                     46148         4704850,95
         2001 Peripherals and Accessories                                     71162         7859707,71
         1998 Electronics                                                      9853         1775688,87
         1998 Peripherals and Accessories                                     69751         9073257,37
         1999 Photo                                                           23394         3884664,22
         2000 Hardware                                                         3088          4336456,4
         2001 Software/Other                                                 102575          3553904,4
         1998 Hardware                                                         4714         6845535,92
         2001 Hardware                                                         4746         5684370,01
         1999 Software/Other                                                 111032         3871433,14

20 rows selected.

So weit, so gut. Nun soll aber pro Jahr nur eine Zeile geliefert und die Produktkategorien und deren jeweiligen Werte in mehreren Spalten dargestellt werden. Das ist dann eine Pivotierung, die Daten müssen quasi um 90° gedreht werden. Für diese Operation müssen die Bezeichnungen der Produktkategorien bekannt sein. Klassisch wird eine solche Transformation mit DECODE und Aggregierung durchgeführt. Man erzeugt neue Abfragespalten, die nur dann einen Wert enthalten, wenn der Wert zur gewünschten Produktkategorie gehört. Andernfalls wird die Spalte mit NULL besetzt. Darauf aufbauend mann man nun die Werte in diesen Spalten aggregieren um zum gewünschten Ergebnis zu kommen.

SQL> select t.CALENDAR_YEAR,
  2         sum(decode(PROD_CATEGORY, 'Photo',       QUANTITY_SOLD, null)) PHOTO_SOLD,
  3         sum(decode(PROD_CATEGORY, 'Electronics', QUANTITY_SOLD, null)) ELECTRONICS_SOLD,
  4         sum(decode(PROD_CATEGORY, 'Hardware',    QUANTITY_SOLD, null)) HARDWARE_SOLD
  5  from sales s
  6  join products p on (s.prod_id = p.prod_id)
  7  join times t on (s.time_id = t.time_id)
  8  group by  t.CALENDAR_YEAR
  9  ;

CALENDAR_YEAR PHOTO_SOLD ELECTRONICS_SOLD HARDWARE_SOLD
------------- ---------- ---------------- -------------
         1999      23394            24105          2809
         1998       9283             9853          4714
         2001      34787            46148          4746
         2000      28045            36161          3088

Die übrigen Produktkategorien bleiben somit außen vor, denn sie wurden im Statement nicht berücksichtigt. Diese Prozedere ist seit Oracle 11g nicht mehr erforderlich, denn mit dieser Version wurde die PIVOT-Klausel eingeführt. Diese ermöglicht die Pivotierung der Ergebnisse wobei auch hier die entsprechenden Werte der Spalte bekannt sein müssen. Kleiner Wermutstropfen ist die Tatsache, dass die PIVOT-Klausel nur auf genau eine Tabelle wirkt. Sollen meherere Tabellen per Join verbunden und dann pivotiert werden, geht der Weg über eine Inline-View.

SQL> select * from (
  2    select t.calendar_year, p.prod_category,  s.quantity_sold
  3    from sales s
  4    join products p on (s.prod_id = p.prod_id)
  5    join times t on (s.time_id = t.time_id)
  6  )
  7  pivot(
  8    sum(QUANTITY_SOLD)
  9    for  PROD_CATEGORY in (
 10      'Photo', 'Electronics', 'Hardware'
 11    )
 12  );

CALENDAR_YEAR    'Photo' 'Electronics' 'Hardware'
------------- ---------- ------------- ----------
         1999      23394         24105       2809
         1998       9283          9853       4714
         2001      34787         46148       4746
         2000      28045         36161       3088

Das Ergebnis is das gleiche wie zuvor. Die Datenbank hat automatisch die Werte aus PROD_CATEGORY zu Spaltennamen umfunktioniert. Alles in allem recht praktisch und vor allem deutlich besser lesbar. 

Was aber, wenn nun mehrere Spalten aggregiert und dann pivotiert werden sollen? Einfach eine weitere Aggregatfunktion in die PIVOT-Klausen aufnehmen? Das geht erst einmal schief.

SQL> select * from (
  2    select p.prod_category, s.amount_sold, s.quantity_sold
  3    from sales s
  4    join products p on (s.prod_id = p.prod_id)
  5    join times t on (s.time_id = t.time_id)
  6  )
  7  pivot(
  8    sum(QUANTITY_SOLD), sum(AMOUNT_SOLD)
  9    for  PROD_CATEGORY in (
 10      'Photo', 'Electronics', 'Hardware'
 11    )
 12  );
select * from (
       *
ERROR at line 1:
ORA-00918: column ambiguously defined

Welche Spalte ist denn hier nicht eindeutig definiert? Die Antwort darauf ist nicht wirklich offensichtlich, aber dennoch logisch. Die Datenbank erstellt aus den Werten der zu pivotierenden Spalte die neuen Spaltennamen. Im Ergebnis entstehen also die gleichen Spaltennamen für beide Summenwerte und sind damit entsprechend nicht eindeutig. Lösen lässt sich das Ganze durch die explizite Benennung der finalen Spalten.

SQL> select * from (
  2    select t.calendar_year, p.prod_category, s.amount_sold, s.quantity_sold
  3    from sales s
  4    join products p on (s.prod_id = p.prod_id)
  5    join times t on (s.time_id = t.time_id)
  6  )
  7  pivot(
  8    sum(QUANTITY_SOLD) qty_sold, sum(AMOUNT_SOLD) amt_sold
  9    for  PROD_CATEGORY in (
 10      'Photo', 'Electronics', 'Hardware'
 11    )
 12* );

CALENDAR_YEAR 'Photo'_QTY_SOLD 'Photo'_AMT_SOLD 'Electronics'_QTY_SOLD 'Electronics'_AMT_SOLD 'Hardware'_QTY_SOLD 'Hardware'_AMT_SOLD
------------- ---------------- ---------------- ---------------------- ---------------------- ------------------- -------------------
         1999            23394       3884664,22                  24105             2916369,92                2809          3781243,27
         1998             9283       3414323,88                   9853             1775688,87                4714          6845535,92
         2001            34787       6333628,91                  46148             4704850,95                4746          5684370,01
         2000            28045       4329248,89                  36161             5200605,88                3088           4336456,4

Die Spalten werden also nun aus dem Wert sowie dem Spaltenalias zusammengesetzt und sind somit wieder eindeutig.

Obwohl die Funktion schon sehr lange in der Oracle Datenbank verfügbar ist, ist es dennoch ein vielleicht zu wenig beachtetes Feature, dass durchaus seine Daseinsberechtigung hat.