Muniqsoft Training

Auswahl  

Pivoting in 11g - Zeilen zu Spalten 

Oracle
SQL
RDBMS 12.x
14.12.18 (MP)
14.12.18 (MP)
SQL

Body

Eine immer wiederkehrende Fragestellung - z.B. bei der Erstellung von Berichten - lautet vereinfacht: "Wie mache ich Zeilen zu Spalten?" Das heisst, Sie haben normalisierte Tabellen, wollen aber den Inhalt gruppieren und in Form einer Kreuztabelle (auch Pivot Tabelle genannt) darstellen.

Ein sehr vereinfachtes Beispiel soll demonstrieren, was damit gemeint ist. Sie haben folgende Tabelle mit Verkaufszahlen:

ID    KUNDE      PRODUKT      MENGE
---   --------   --------     -----
  1   Kunde A    Kalender       100
  2   Kunde B    Block           40
  3   Kunde C    Heft            70
  4   Kunde D    Block          100
  5   Kunde A    Block          200
  6   Kunde D    Heft            30


Diese wurde durch folgendes Skript erzeugt:

CREATE TABLE VERKAUF
(
ID      NUMBER CONSTRAINT PK_VERKAUF PRIMARY KEY,
KUNDE   VARCHAR2(100) NOT NULL,
PRODUKT VARCHAR2(100) NOT NULL,
MENGE   NUMBER NOT NULL
)
/
INSERT INTO VERKAUF
VALUES(1, 'Kunde A', 'Kalender', 100);
INSERT INTO VERKAUF
VALUES(2, 'Kunde B', 'Block', 40);
INSERT INTO VERKAUF
VALUES(3, 'Kunde C', 'Heft', 70);
INSERT INTO VERKAUF
VALUES(4, 'Kunde D', 'Block', 100);
INSERT INTO VERKAUF
VALUES(5, 'Kunde A', 'Block', 200);
INSERT INTO VERKAUF
VALUES(6, 'Kunde D', 'Heft', 30);


Nun hätten Sie gerne folgende Darstellung:

KUNDE     KALENDER      BLOCK               HEFT
--------  ----------    ----------    ----------
Kunde A          100           200    
Kunde B                         40    
Kunde C                                       70
Kunde D                        100            30


Das geht auch schon vor Version 11g, z.B. durch folgende Anweisung:

SELECT kunde,
   SUM(DECODE (produkt, 'Kalender', menge, NULL)) Kalender,
   SUM(DECODE (produkt, 'Block', menge, NULL)) Block,
   SUM(DECODE (produkt, 'Heft', menge, NULL)) Heft
  FROM VERKAUF
 GROUP BY kunde
 ORDER BY kunde;

Zugegeben - nicht sonderlich intuitiv, nicht gut lesbar und einiges an Tipparbeit - aber es funktioniert.

In 11g wurde für solche Zwecke eigens eine neue Klausel eingeführt - PIVOT. Ein Select, der das gleiche Ergebnis (mit einem kleinen Schönheitsfehler, s.u. ) liefert, würde dann so aussehen:

SELECT * FROM
   (SELECT kunde, produkt, menge FROM VERKAUF)
  PIVOT
  (
   SUM(menge)
   FOR produkt
   IN ('Kalender', 'Block', 'Heft')
  )
ORDER BY kunde;


Sehen wir uns die Syntax genauer an. Innerhalb der PIVOT-Klausel müssen Sie angeben:

  • Die Art der Aggregierung(en) - hier SUM(menge)

  • FOR-Klausel: Die Inhalte welcher Spalte zu Zeilen werden sollen - hier produkt

  • IN-Klausel: Welche Werte innerhalb dieser Spalte verwendet werden sollen - hier 'Kalender', 'Block' und 'Heft'

Nach allen weiteren im Select angegebenen Spalten wird implizit gruppiert - hier also nach kunde. Sobald Sie nur nach bestimmten Spalten gruppieren wollen (was praktisch immer der Fall ist), müssen Sie mit einer Inline View (wie hier gezeigt) oder alternativ mit der WITH-Klausel arbeiten.

Der oben angesprochene Schönheitsfehler liegt darin, dass die Spaltenüberschriften genau den angegebenen Werten entsprechen, inklusive Hochkommata. Um das zu umgehen - oder weil Sie sowieso andere Überschriften wollen - können Sie mit Spaltenaliasen arbeiten, wie Sie es gewohnt sind:

SELECT * FROM
   (SELECT kunde, produkt, menge FROM VERKAUF)
  PIVOT
  (
   SUM(menge)
   FOR produkt
   IN ('Kalender' AS kalender,
       'Block' block,
       'Heft' "Anzahl Hefte")
  )
ORDER BY kunde;


Sie können auch, durch Kommata getrennt, mehrere Aggregatsfunktionen angeben; in diesem Fall müssen Sie zusätzlich einen Alias mit angeben, der dann an die Spaltenüberschrift angehängt wird:

SELECT * FROM
   (SELECT produkt, menge
     FROM VERKAUF )
  PIVOT
  (
   SUM(menge) summe,
   COUNT(menge) anzahl
   FOR produkt
   IN ('Kalender' as kal, 'Block' as Block, 'Heft' as Heft)
  );

 

KAL_SUMME KAL_ANZAHL BLOCK_SUMME BLOCK_ANZAHL HEFT_SUMME HEFT_ANZAHL
--------- ---------- ----------- ------------ ---------- -----------
      100          1         340            3        100           2


Eine Einschränkung bleibt: Sie können nicht dynamisch arbeiten, sondern müssen explizit die Werte angeben.

Gibt man das Schlüsselwort XML mit an, so erhält man statt einzelner Spalten für jeden Wert eine einzige neue Spalte vom Typ XMLType, die alle Wertepaare als XML-Fragment beinhaltet. Hier hat man die Wahl zwischen dem Schlüsselwort ANY und einer Unterabfrage. Die explizite Angabe einzelner Werte ist in diesem Fall wiederum nicht zulässig. ANY steht als Platzhalter für alle Werte, die in der Spalte vorkommen. Der Unterschied in der Ausgabe liegt darin, dass bei einer Unterabfrage im erzeugten XML grundsätzlich alle Produkte erscheinen, auch wenn kein Eintrag für das entsprechende Produkt da ist, während bei ANY nur diejenigen Produkte erscheinen, für die es einen Wert gibt, in folgenden nur gezeigt für Kunde B. Beachten Sie, dass die PIVOT-Klausel noch vor der WHERE-Klausel stehen muss:

COL PRODUKT_XML FOR a50
SET LONG 2000
SELECT * FROM
   (SELECT kunde, produkt, menge FROM VERKAUF)
  PIVOT XML
  (
   SUM(menge) menge
   FOR produkt
   IN (ANY)
  )
WHERE kunde = 'Kunde B';

 

KUNDE      PRODUKT_XML
---------- --------------------------------------------------
Kunde B    <PivotSet><item><column name = "PRODUKT">Block</co
           lumn><column name = "MENGE">40</column></item></Pi
           votSet>

-- bzw:

SELECT * FROM
   (SELECT kunde, produkt, menge FROM VERKAUF)
  PIVOT XML
  (
   SUM(menge) menge
   FOR produkt
   IN (SELECT DISTINCT produkt FROM VERKAUF)
  )
WHERE kunde = 'Kunde B';

 

KUNDE      PRODUKT_XML
---------- --------------------------------------------------
Kunde B    <PivotSet><item><column name = "PRODUKT">Block</co
           lumn><column name = "MENGE">40</column></item><ite
           m><column name = "PRODUKT">Heft</column><column na
           me = "MENGE"></column></item><item><column name =
           "PRODUKT">Kalender</column><column name = "MENGE">
            </column></item></PivotSet>


UNPIVOT – Spalten zu Zeilen

Die Umkehrung - Spalten als Zeilen ausgeben - ist ab 11g auch sehr leicht möglich. Gehen wir von folgender Tabelle aus:

KUNDE     KALENDER    BLOCK       HEFT
--------  ----------  ----------  ----------
Kunde A          100        200    
Kunde B                      40    
Kunde C                                   70
Kunde D                     100           30


Erzeugt wurde sie durch:

CREATE TABLE REPORT AS
SELECT * FROM
   (SELECT kunde, produkt, menge FROM VERKAUF)
  PIVOT
  (
   SUM(menge)
   FOR produkt
   IN ('Kalender' AS kalender,
       'Block' block,
       'Heft' heft)
  )
ORDER BY kunde;


Will man vor 11g aus Artikel- und zugehörigen Mengenangaben Einzeleinträge machen, so ist das zwar machbar, aber umständlich und sehr imperformant, da die Tabelle mehrfach gelesen werden muss:

SELECT kunde, 'KALENDER', kalender FROM REPORT
-- WHERE kalender IS NOT NULL
UNION ALL
SELECT kunde, 'BLOCK', block FROM REPORT
-- WHERE block IS NOT NULL
UNION ALL
SELECT kunde, 'HEFT', heft FROM REPORT
-- WHERE heft IS NOT NULL

;


Hier ist die neue UNPIVOT-Klausel wesentlich einfacher:

SELECT * FROM REPORT
  UNPIVOT
  (
   menge
   FOR produkt
   IN (kalender, block, heft)
  );

 

KUNDE      PRODUKT       MENGE
---------- -------- ----------
Kunde A    KALENDER        100
Kunde A    BLOCK           200
Kunde B    BLOCK            40
Kunde C    HEFT             70
Kunde D    BLOCK           100
Kunde D    HEFT             30


Werfen wir auch hier einen Blick auf die Syntax. Angegeben werden muss hier:

  • Eine Spaltenüberschrift für die Werte - hier menge

  • Eine Spaltenüberschrift für die Spalten, die zu Zeilen werden sollen - hier produkt

  • Eine Liste der Spalten, die einbezogen werden sollen - hier kalender, block, heft

Auch hier gilt: Soll nur ein Teil der Spalten ausgegeben werden, so muss mit einer Unterabfrage gearbeitet werden.

Obiger Select beispielsweise wäre folgendermaßen umzuwandeln, wenn die Tabelle noch mehr Spalten enthielte:

SELECT * FROM
   (SELECT kunde, kalender, block, heft FROM REPORT)
  UNPIVOT
  (
   menge
   FOR produkt
   IN (kalender, block, heft)
  );


Standardmäßig werden für NULL-Werte keine Zeilen ausgegeben. Diese können aber optional angefordert werden durch INCLUDE NULLS:

SELECT * FROM REPORT
  UNPIVOT INCLUDE NULLS
  (
   menge
   FOR produkt
   IN (kalender, block, heft)
  );
 
KUNDE      PRODUKT       MENGE
---------- -------- ----------
Kunde A    KALENDER        100
Kunde A    BLOCK           200
Kunde A    HEFT
Kunde B    KALENDER
Kunde B    BLOCK            40
Kunde B    HEFT
Kunde C    KALENDER
Kunde C    BLOCK
Kunde C    HEFT             70
Kunde D    KALENDER
Kunde D    BLOCK           100
Kunde D    HEFT             30


Wird statt INCLUDE NULLS angegeben EXCLUDE NULLS, so entspricht dies dem Standardverhalten.

Besuchen Sie uns doch bei einer unsere über 40 Oracle Schulungen in München - Unterhaching.