PIVOT und UNPIVOT Operationen einfach gemacht

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
 

Visit the Oracle Blog

 

Contact Us

Oracle

Integrated Cloud Applications & Platform Services