Das Gegenstück zu PIVOT: UNPIVOT

Passend zur PIVOT-Klausel gibt es dann natürlich auch eine UNPIVOT-Klausel. Diese macht genau das Gegenteil, sie wandelt Spalteln in Zeilen um: die Tabelle wird in die andere Richtung "gekippt". Auch hierfür ein Beispiel:

1. Zunächst eine Beispieltabelle und zwei Zeilen erstellen ...

   create table pivot_table (
     produkt     varchar2(200),
     umsatz_2003 number(15,2),
     umsatz_2004 number(15,2),
     umsatz_2005 number(15,2),
     umsatz_2006 number(15,2)
   )
   /
   
   insert into pivot_table values ('LAPTOP', 10000,20000,30000,40000);
   insert into pivot_table values ('DESKTOP', 20000,null,30000,100000);
   

2. Ein Test zeigt nochmals die Daten an ...

   select * from pivot_table
   /
   
   PRODUKT         UMSATZ_2003 UMSATZ_2004 UMSATZ_2005 UMSATZ_2006
   --------------- ----------- ----------- ----------- -----------
   LAPTOP                10000       20000       30000       40000
   DESKTOP               20000           ?       30000      100000
   

3. Und nun kommt die UNPIVOT-Klausel zum Einsatz - Sie funktioniert ebenso wie die PIVOT-Klausel: Die Spalte PRODUKT kommt aus der "Original-Tabelle", aus den Spaltennamen UMSATZ_2003 bis UMSATZ_2006 werden die Inhalte der Spalte JAHR abgeleitet und die Spalte UMSATZ enthält die Inhalte der besagten vier Original-Spalten.

   select produkt, jahr, umsatz
   from pivot_table
   unpivot(
     umsatz for jahr in (
       "UMSATZ_2003" as 2003, 
       "UMSATZ_2004" as 2004, 
       "UMSATZ_2005" as 2005, 
       "UMSATZ_2006" as 2006
     )
   )
   /
   
   PRODUKT             JAHR   UMSATZ
   --------------- -------- --------
   LAPTOP              2003    10000
   LAPTOP              2004    20000
   LAPTOP              2005    30000
   LAPTOP              2006    40000
   DESKTOP             2003    20000
   DESKTOP             2005    30000
   DESKTOP             2006   100000
   

4. Mit der INCLUDE NULLS-Klausel können nun noch die NULL-Werte (hier sind das die Umsätze für DESKTOP im Jahr 2004) einbezogen werden.

   select produkt, jahr, umsatz
   from pivot_table
   unpivot include nulls(
     umsatz for jahr in (
       "UMSATZ_2003" as 2003, 
       "UMSATZ_2004" as 2004, 
       "UMSATZ_2005" as 2005, 
       "UMSATZ_2006" as 2006
     )
   )
   /
   
   PRODUKT             JAHR   UMSATZ
   --------------- -------- --------
   LAPTOP              2003    10000
   LAPTOP              2004    20000
   LAPTOP              2005    30000
   LAPTOP              2006    40000
   DESKTOP             2003    20000
   DESKTOP             2004
   DESKTOP             2005    30000
   DESKTOP             2006   100000
   

Das ganze Thema Kreuztabellen wird mit Oracle11g also wesentlich leichter ... mit den Lösungen, die wir bislang dafür hatten, werden wir auf den Versionen bis Oracle10g allerdings auch noch eine ganze Weile arbeiten ...

Kreuztabellen in Oracle11g: SQL PIVOT-Klausel

Vor einiger Zeit habe ich in diesem Blog einen Tipp zum Thema Kreuztabellen erzeugen veröffentlicht. In Oracle11g gibt es dazu ein neues SQL-Konstrukt: Die PIVOT bzw. UNPIVOT-Klausel. Der alte Tipp bleibt natürlich gültig - schließlich gibt es diese Klauseln in Oracle9i oder Oracle10g noch nicht.

Ein Beispiel: Der folgende Output ist erwünscht:

DEPTNO SUM(CLERK) SUM(SALESMAN) SUM(MANAGER)
---------- ---------- ------------- ------------
    30        950          5600         2850
    20       1900                       2975
    10       1300                       2450

Wie bekommt man das hin? Ganz einfach in zwei Schritten:

1. Zunächst wird die EMP-Tabelle "gekippt". Die einzelnen Werte der Spalte JOB (SALESMAN,CLERK,MANAGER) sollen neue Spalten der Pivot-Tabelle werden:

   select deptno, clerk, salesman, manager
   from emp pivot (
   sum(sal) for JOB
   in ('CLERK' as "CLERK", 'SALESMAN' as "SALESMAN", 'MANAGER' as "MANAGER")
   )
   
   DEPTNO      CLERK   SALESMAN    MANAGER
   ---------- ---------- ---------- ----------
       10                             2450
       30        950
       30                  1250
       20                             2975
       30                             2850
       10
       20
       20
       20       1100
       30                  1500
       20        800
       30                  1600
       30                  1250
       10       1300
   

2. Dann nur noch nach DEPTNO summieren:

   select deptno, sum(clerk), sum(salesman), sum(manager)
   from emp pivot (
   sum(sal) for JOB
   in ('CLERK' as "CLERK", 'SALESMAN' as "SALESMAN", 'MANAGER' as "MANAGER")
   )
   group by deptno
   
   DEPTNO SUM(CLERK) SUM(SALESMAN) SUM(MANAGER)
   ---------- ---------- ------------- ------------
       30        950          5600         2850
       20       1900                       2975
       10       1300                       2450
   

   Man sieht also: Kreuztabellen sind in Oracle11g einfach nur ... einfach!

Bernutzereingaben in SQL*Plus ...

Wenn ein SQL*Plus-Skript Eingaben erwartet, kann man diese auch "benutzerfreundlich" alle auf einmal zu Beginn abfragen. Dazu gibt es das ACCEPT-Kommando ... Standardwerte können ebenso mitgegeben werden wie ein Text ... Ein Beispiel:

accept TABNAME default 'MEINE_TABELLE' prompt 'Bitte Tabellennamen angeben [MEINE_TABELLE] '

drop table &TABNAME.
/

create table &TABNAME. (
 id number(10),
 :
)
/

Insbesondere für Installationsskripte ist dies sehr praktisch. Zu Beginn kann man so alle nötigen Parameter abfragen ... Beim nächsten Mal geht's dann wieder mit Neuigkeiten zu Oracle11g weiter.

Tabellen in Oracle11g: "Virtual Columns"

Man weiss bei den vielen neuen Funktionen in Oracle11g gar nicht recht, wo man anfangen soll - starten wir einfach mit einem recht netten Feature beim Erstellen von Tabellen: Virtual Columns.

SQL> create table test_virtual (
  2    col number(10),
  3    virtcol number(10) as (col * 2)
  4  );

Table created.

SQL> select * from test_virtual;

no rows selected

SQL> insert into test_virtual (col) values (2);

1 row created.

SQL> select * from test_virtual;

       COL    VIRTCOL
---------- ----------
         2          4

Eine virtuelle Spalte wird aus den anderen Spalten berechnet. Wenn man es genau nimmt, kann die Oracle-Datenbank dies schon länger - die Funktionalität war für den Anwender lediglich nicht zugänglich. Mit Oracle11g wurden die virtual Columns nun für die Nutzung durch Anwendungsentwickler freigegeben. Ein Index auf eine virtual Column verhält sich übrigens wie ein ganz normaler function based index. Mehr zum Thema in der Dokumentation: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_7002.htm#BABIJIHJ

Oracle11g zum Download verfügbar

Seite heute kann die neue Datenbankversion Oracle11g aus dem Oracle Technet heruntergeladen werden. Zunächst steht die Version für Linux x86 bereit - andere werden folgen ... http://www.oracle.com/technology/products/database/oracle11g/index.html Dokumentation ist ebenfalls verfügbar: http://www.oracle.com/pls/db111/portal.portal_db?selected=3 Viel Spaß beim Ausprobieren