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.