Kreuztabellen erzeugen mit Oracle geht ganz einfach, schliesslich gibt es die PIVOT und UNPIVOT Funktionen in Oracle.
Viele Informationen und Blogeinträge sind dazu auch im Internet zu lesen, trotzdem tauchen immer mal wieder Fragen zu dem Thema auf.
Aus diesem Grund werden in folgenden Abschnitten einige wichtige Funktionen an Beispielen demonstriert. Am Ende findet sich auch die Möglichkeit
die verwendeten SQL Skripte zu laden.
Starten wir mit der Syntax: Generell sieht die Verwendung folgendermaßen aus:
[... FROM table] PIVOT [pivot_for][pivot_in]
[... FROM table] UNPIVOT [unpivot_for][unpivot_in]
Beginnen wir mit der Beschreibung der PIVOT Funktionalität. Die PIVOT Klausel hilft tabellenübergreifende Abfragen zu tätigen, um Zeilen in Spalten umzuwandeln. Um die Funktionsweise an einem ganz einfachen Tabellenmodell zu erklären, wird die Tabelle EMP verwendet. Das erste Beispiel zu PIVOT summiert das Gehalt der Angestellten, deren Job Clerk oder Salesman ist. Die Spaltennamen sollen dabei Clerk und Salesman lauten.
SQL> SELECT * FROM (SELECT deptno, sal, job FROM emp) PIVOT (sum(sal) FOR job IN ( 'CLERK' AS "Clerk",'SALESMAN' AS "Salesman")) order by deptno; DEPTNO Clerk Salesman ---------- ---------- ---------- 10 1300 20 1900 30 950 5600
In der PIVOT Klausel wird dabei zu Beginn die angegebene Aggregationsfunktion berechnet. Es können auch mehr als eine Aggregatsfunktion verwendet werden um weitere Spalten auzusgegeben. Im nächsten Beispiel wird das Minimum- und Maximum-Gehalt einer Berufsgruppe (hier auch wieder zu Clerk und Salesman) berechnet.
SQL> SELECT * FROM (SELECT deptno, sal, job FROM scott.emp) PIVOT (min(sal) as min, max(sal) as max FOR job IN ('CLERK' AS "Clerk",'SALESMAN' AS "Salesman")) ORDER BY deptno; DEPTNO Clerk_MIN Clerk_MAX Salesman_MIN Salesman_MAX ---------- ---------- ---------- ------------ ------------ 10 1300 1300 20 800 1100 30 950 950 1250 1600
Aber wie geht man mit einer dynamischen PIVOT_IN Klausel um? In unserem Beispiel sollen die Berufsgruppen über eine Subquery bestimmt werden. Dazu ist das zusätzliche Schlüsselwort XML erforderlich, das entweder eine Subquery oder das Wildcard-Schlüsselwort ANY erlaubt. Die Ausgabe ist allerdings, wie der Syntax schon andeutet, in XML Form.
Um die XML Ausgabe zu formatieren, kann man die Funktion EXTRACT verwenden. Die XPATH Zeichenabfolge im zweiten Argument von EXTRACT wird auf das XML angewendet, um das Ergebnis zu lokalisieren und das entsprechende XML Frament auszugegeben. Das Ergebnis sieht dann folgendermaßen aus:
SQL> col job1 format a10 SQL> col job2 format a10 SQL> col job3 format a10 SQL> col job4 format a10 SQL>WITH u AS (SELECT * FROM ( SELECT job, deptno, sal FROM emp ) PIVOT XML (sum(sal) FOR job IN (select distinct job from emp))) SELECT deptno, extract(job_xml,'/PivotSet/item[1]/column[2]/text()') job1, extract(job_xml,'/PivotSet/item[2]/column[2]/text()') job2, extract(job_xml,'/PivotSet/item[3]/column[2]/text()') job3, extract(job_xml,'/PivotSet/item[4]/column[2]/text()') job4 FROM u; DEPTNO JOB1 JOB2 JOB3 JOB4 ---------- ---------- ---------- ---------- ---------- 10 1300 2450 5000 20 6000 1900 2975 30 950 2850
Wie schnell zu erkennen ist, muss bei dieser Abfrage die Anzahl der Spalten bekannt sein. Das bedeutet man
muss das Ergebnis der Subquery vorab kennen. Somit ist diese Lösung nur eingeschränkt verwendbar.
Da das XML Ergebnis allerdings immer die gleiche XML Struktur aufweist, könnte man das Ganze auch mit einer Table Function lösen.
Um eine optimale Verarbeitung zu garantieren, bietet sich hier die Verwendung von Pipelined Functions an.
Zuerst legen wir die dazugehörigen Objekttypen T_PIVOT und T_PIVOT_TAB an, die das Ergebnis enthalten sollen.
SQL> CREATE TYPE t_pivot AS OBJECT ( deptno NUMBER(2), job VARCHAR2(50), sum_sal NUMBER); / Type created. SQL> CREATE TYPE t_pivot_tab IS TABLE OF t_pivot; / Type created.
Die Pipelined Function berechnet dann das Ergebnis wie folgt:
CREATE OR REPLACE FUNCTION from_xml RETURN t_pivot_tab PIPELINED AS v_xml XMLTYPE; v_item_xml XMLTYPE; v_index NUMBER; v_sum_sal NUMBER; CURSOR c_deptno_items IS SELECT deptno, job_xml FROM (SELECT job, deptno, sal FROM emp) PIVOT XML (SUM(sal) as "SUM_SAL" FOR (job) IN (SELECT DISTINCT job FROM emp)); BEGIN FOR v_rec IN c_deptno_items LOOP v_xml := v_rec.job_xml; v_index := 1; LOOP v_item_xml := v_xml.EXTRACT('/PivotSet/item[' || v_index || ']'); EXIT WHEN v_item_xml IS NULL; v_index := v_index + 1; IF v_item_xml.EXTRACT('/item/column[@name="SUM_SAL"]/text()') IS NOT NULL THEN v_sum_sal:= v_item_xml.EXTRACT('/item/column[@name="SUM_SAL"]/text()').getNumberVal(); ELSE v_sum_sal := 0; END IF; PIPE ROW(t_pivot(v_rec.deptno, v_item_xml.EXTRACT('/item/column[@name="JOB"]/text()').getStringVal(), v_sum_sal)); END LOOP; END LOOP; END; / Function created.
Das gewünschte Ergebnis sieht dann so aus:
SQL> col job format a10 SQL> select deptno, job, sum_sal FROM from_xml(); DEPTNO JOB SUM_SAL ---------- ---------- ---------- 10 ANALYST 0 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 SALESMAN 0 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 20 PRESIDENT 0 20 SALESMAN 0 30 ANALYST 0 30 CLERK 950 30 MANAGER 2850 30 PRESIDENT 0 30 SALESMAN 5600 15 rows selected.
Hinweis: Seit 12.2 kann man das Schlüsselwort TABLE nach der FROM Klausel wie in unserem Beispiel gezeigt einfach weglassen.
Wie sieht es nun mit der UNPIVOT Funktionalität aus? Die UNPIVOT Operation ist der Counterpart von PIVOT und wandelt Spalten in Zeilen um.
Um ein sinnvolles Beispiel zu UNPIVOT und der EMP Tabelle zu zeigen, fügen wir Key-Value Paare zur Ausgabe hinzu, wobei der Key
beispielsweise den Wert JOB beinhaltet und die Value Spalte die zugehörigen Job Eintragungen ausgibt.
SQL> SELECT deptno, empno, key, value FROM emp UNPIVOT (value FOR key IN (job)) order by 1; DEPTNO EMPNO KEY VALUE ---------- ---------- --------- --------------------------- 10 7782 JOB MANAGER 10 7839 JOB PRESIDENT 10 7934 JOB CLERK 20 7566 JOB MANAGER 20 7902 JOB ANALYST 20 7876 JOB CLERK 20 7369 JOB CLERK 20 7788 JOB ANALYST 30 7521 JOB SALESMAN 30 7844 JOB SALESMAN 30 7499 JOB SALESMAN 30 7900 JOB CLERK 30 7698 JOB MANAGER 30 7654 JOB SALESMAN 14 rows selected.
Um weitere Key Value Paare zu erzeugen, hängt man einfach weitere UNPIVOT Klauseln an.
SQL> set linesize window SQL> SELECT deptno, empno, key, value, key1, value1 FROM emp UNPIVOT (value FOR key IN (job)) UNPIVOT (value1 FOR key1 in (hiredate)) order by 1; DEPTNO EMPNO KEY VALUE KEY1 VALUE1 ---------- ---------- --------- --------------------------- ------------------------ --------------- 10 7782 JOB MANAGER HIREDATE 09-JUN-81 10 7839 JOB PRESIDENT HIREDATE 17-NOV-81 10 7934 JOB CLERK HIREDATE 23-JAN-82 20 7566 JOB MANAGER HIREDATE 02-APR-81 20 7902 JOB ANALYST HIREDATE 03-DEC-81 20 7876 JOB CLERK HIREDATE 23-MAY-87 20 7369 JOB CLERK HIREDATE 17-DEC-80 20 7788 JOB ANALYST HIREDATE 19-APR-87 30 7521 JOB SALESMAN HIREDATE 22-FEB-81 30 7844 JOB SALESMAN HIREDATE 08-SEP-81 30 7499 JOB SALESMAN HIREDATE 20-FEB-81 30 7900 JOB CLERK HIREDATE 03-DEC-81 30 7698 JOB MANAGER HIREDATE 01-MAY-81 30 7654 JOB SALESMAN HIREDATE 28-SEP-81 14 rows selected.
Lust das Ganze selbst einmal auszuprobieren? Die Beispielskripte lassen sich hier herunterladen .
Da eine einfache Suche im Internet viele Ergebnisse zum Thema PIVOT und UNPIVOT liefert, sind im folgenden nur die Informationen aus dem Handbuch gelistet.
Informationen
Zurück zur Community-Seite