30. August 2007

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 ...

28. August 2007

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!

23. August 2007

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.

14. August 2007

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

10. August 2007

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

Beliebte Postings