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

Kommentare:

Sabine hat gesagt…

Hallo Carsten,

ich werde gerade aus Blogs und Doku nicht schlau.

Wie bekomme ich eine weitere Spalte mit Werten hintendran, z. B. Gewinn?

PRODUKT JAHR UMSATZ GEWINN
--------------- -------- -------- ------
LAPTOP 2003 10000 1000
LAPTOP 2004 20000 2000
LAPTOP 2005 30000 3000
LAPTOP 2006 40000 4000
DESKTOP 2003 20000 2000
DESKTOP 2004
DESKTOP 2005 30000 3000
DESKTOP 2006 100000 10000

In der Quelle könnte ich dafür zusätzliche Spalten anlegen oder zusätzliche Zeilen mit den gewünschten Werten einfügen.

Im Moment 'unpivote' ich zuerst Gewinn und Umsatz getrennt und mache danach einen Join über Produkt und Jahr.
Aber irgendwie habe ich das Gefühl dass es auch anders gehen muss!

Hast Du einen Tipp?

Carsten Czarski hat gesagt…

Hallo Sabine,

kannst Du nochmals dazustellen, was Deine Quelldaten sind, und was das Ziel sein soll ...? So richtig werde ich aus Deiner Frage noch nicht schlau ...

Danke und beste Grüße

-Carsten

Beliebte Postings