Insbesondere in Geschäftsberichten wird häufig die Darstellung als Kreuztabelle gewünscht. 

Abbildung 1 zeigt den Unterschied zwischen einem normalen Application Express-Bericht und der Darstellung als Kreuztabelle. In der Kreuztabelle werden die Daten im Grunde genommen "gekippt". Die Umsätze pro Tag, die in einem normalen Bericht durch Zeilen repräsentiert werden, werden nun durch Spalten repräsentiert. Die Herausforderung besteht darin, dass die eigentlichen Tabellendaten fast immer in "normaler" Form (also dem linken Bericht entsprechend) vorliegen (Abbildung 2).

Abb.2: Betrachten der Tabellendaten im SQL Workshop

Vorbereitungen

Legen Sie sich eine Tabelle an und füllen Sie diese mit einigen Testdatensätzen. Natürlich können Sie diesen Tipp auch direkt mit eigenen Daten nachvollziehen.

create table umsatz(
 produkt varchar2(200),
 datum date,
 umsatz number(10,2)
)
/

insert into umsatz values ('CD Rohlinge',  to_date('2006-01-23', 'YYYY-MM-DD'), 200.22);
insert into umsatz values ('CD Brenner',   to_date('2006-01-23', 'YYYY-MM-DD'), 1928.90);
insert into umsatz values ('DVD Rohlinge', to_date('2006-01-24', 'YYYY-MM-DD'), 150.00);
insert into umsatz values ('CD Brenner',   to_date('2006-01-26', 'YYYY-MM-DD'), 1000);
insert into umsatz values ('DVD Rohlinge', to_date('2006-01-27', 'YYYY-MM-DD'), 100);
insert into umsatz values ('CD Rohlinge',  to_date('2006-01-27', 'YYYY-MM-DD'), 200);

Erstellen Sie nun eine Application Express-Anwendung. Auf die erste Seite legen Sie zwei Felder zur Datumsauswahl (P1_START_DATUM und P1_END_DATUM) und einen Bericht auf die neue Tabelle. Als SQL hinterlegen Sie ...

select * from umsatz
where datum between to_date(:P1_START_DATUM, 'DD.MM.YYYY') and to_date(:P1_END_DATUM ,'DD.MM.YYYY') 

Das Ergebnis sollte wie in Abbildung 3 aussehen.

Abb.3: 1. Schritt: Erstellen eines einfachen Berichts

Nun wurde nicht für jedes Produkt an jedem Tag Umsatz erzielt. Um am Ende zur Kreuztabelle zu kommen, benötigen wir als Zwischenschritt auch die Kombinationen zwischen Produkt und Datum, für die es keinen Umsatz gibt. Hier hilft der Partitioned Outer Join weiter. Partitioned Outer Joins haben nichts mit der Partitioning-Option der Datenbank zu tun - sie sind ein Teil des normalen SQL Sprachumfangs. Weitere Informationen zu diesem Thema finden Sie in der Oracle-Dokumentation (Data Warehousing Guide).

Um den Partitioned Outer Join durchführen zu können, benötigen wir noch eine Tabelle mit einer Zeile für jedes Datum. Das nachfolgende Skript legt eine solche an und füllt sie mit je einer Zeile für jedes Datum im Jahr 2006. Führen Sie die Kommandos im SQL Workshop aus.

create table datum(
 datum date
)
/

declare
 v_startdate date := to_date('2006-01-01','YYYY-MM-DD');
 v_enddate   date := to_date('2006-12-31','YYYY-MM-DD');
 v_currdate  date := v_startdate;
begin
 while v_currdate <= v_enddate loop
 insert into datum values (v_currdate);
 v_currdate := v_currdate + 1;
 end loop;
end;
/

Ändern Sie nun Ihren Bericht. Er soll nun zusätzlich zu den Umsätzen Zeilen für die Tage ohne Umsätze erhalten.

select 
 u.produkt,
 datum,
 u.umsatz
from umsatz u
partition by (produkt) right outer join datum d using (datum)
where datum between to_date(:P1_START_DATUM, 'DD.MM.YYYY') and to_date(:P1_END_DATUM ,'DD.MM.YYYY')

In dem SQL-Kommando erkennen Sie zunächst einen normalen Right Outer Join. Die Klausel PARTITION BY (PRODUCT) bewirkt, dass diese Operation für jedes Produkt vorgenommen wird. Abbildung 4 zeigt das Ergebnis.

Abb.4: 2. Schritt: "Partitioned Outer Join"

Einen Partitioned Outer Join benötigen Sie übrigens nicht nur zur Erstellung einer Kreuztabelle. Auch wenn Sie ein Diagramm erstellen möchten, ist diese Technik hilfreich; schließlich benötigen Sie auch hier Platzhalter für die Tage ohne Umsatz. Wenn Sie, wie vor einiger Zeit auf dieser Seite dargestellt, gleitende Durchschnitte berechnen möchten, sind Partitioned Outer Joins ebenfalls sehr wichtig.

Nun sind die Daten vorbereitet - Es fehlt "nur" noch das eigentliche "Kippen" der (Pivoting) der Tabelle. Und dies stellen wir im folgenden zweimal vor: Die Nutzer von Oracle11g können die Kreuztabelle auf einfachstem Wege mit der neuen SQL PIVOT-Klausel erzeugen; für die Nutzer von Oracle9i und Oracle10g stellen wir anschließend eine PL/SQL-Prozedur vor.

Kreuztabellen in Oracle11g

Oracle11g unterstützt Kreuztabellen out-of-the-box als Teil der SQL-Abfragesprache. Mit der neuen PIVOT-Klausel wird definiert, welche "neuen" Spalten in der Ergebnismenge mit welchen Daten erzeugt werden sollen. So wird in der nachfolgenden SQL-Abfrage festgelegt, dass die Umsatzdaten des Datums P1_START_DATE in der neuen Spalte TAG_1, die des Datums P1_START_DATE + 1 (also plus 1 Tag) in der neuen Spalte TAG_2 und fortfolgend dargestellt werden sollen. Wieviele Spalten dargestellt werden sollen, ist in der SQL-Abfrage wiederum festgelegt (hier: fünf).

Wenn Sie also auf einer Oracle11g-Datenbank arbeiten, können Sie einfach einen neuen SQL-Bericht mit nachfolgender Abfrage erstellen und direkt bei den Spaltenüberschriften weitermachen.

with umsatzdaten as (
  select 
    u.produkt,
    datum,
    u.umsatz
  from umsatz u
  partition by (produkt) right outer join datum d using (datum)
  where datum between to_date(:P1_START_DATUM, 'DD.MM.YYYY') and to_date(:P1_START_DATUM ,'DD.MM.YYYY') + 4
)
select 
  produkt,
  tag_1,  
  tag_2,
  tag_3,
  tag_4,
  tag_5
from umsatzdaten 
  -- Hier beginnt die PIVOT-Klausel ... 
  pivot (
    -- In den "Zellen" stehen die Umsatzdaten
    sum(umsatz) for datum in (
      -- Die Werte des Datums P1_START_DATE kommen die neue Spalte TAG_1
      to_date('&P1_START_DATUM.', 'DD.MM.YYYY') as "TAG_1", 
      -- Die Werte des Datums P1_START_DATE + 1 (plus 1 Tag) kommen die neue Spalte TAG_2
      to_date('&P1_START_DATUM.', 'DD.MM.YYYY') + 1 as "TAG_2",
      -- ... und so weiter ...
      to_date('&P1_START_DATUM.', 'DD.MM.YYYY') + 2 as "TAG_3",
      to_date('&P1_START_DATUM.', 'DD.MM.YYYY') + 3 as "TAG_4",
      to_date('&P1_START_DATUM.', 'DD.MM.YYYY') + 4 as "TAG_5"
    )
  )

Kreuztabellen vor Oracle11g

Wenn Sie auf einer Oracle9i oder Oracle10g-Datenbank arbeiten, steht Ihnen die PIVOT-Klausel nicht zur Verfügung. Doch das ist nicht weiter tragisch: Mit ein wenig PL/SQL-Code können Sie genau das gleiche erreichen.

Erzeugen Sie zunächst (entweder im SQL Workshop oder mit SQL*Plus) die folgende PL/SQL-Funktion GETPIVOTSQL - sie wird die Aufgabe übernehmen, die richtige SQL-Abfrage für die gewünschte Kreuztabelle zu generieren.

create or replace function getPivotSql(
  p_sql            in varchar2,
  p_x_col          in varchar2,
  p_x_col_type     in varchar2 default 'DATE',
  p_x_col_start    in varchar2,
  p_x_col_interval in varchar2,
  p_x_col_int_unit in varchar2 default 'DAY',
  p_x_col_count    in number,
  p_y_col          in varchar2,
  p_cell_col       in varchar2,
  p_cell_col_aggr  in varchar2 default NULL
) return varchar2
is
  v_sql varchar2(32767);
begin
  v_sql := '';
  v_sql := v_sql || 'with data as ('||chr(10);
  v_sql := v_sql || '  '||p_sql||chr(10);
  v_sql := v_sql || ')';
  if p_x_col_type = 'VARCHAR2' then
    v_sql := v_sql ||', x_dist_values as ('||chr(10);
    v_sql := v_sql ||'  select distinct '||p_x_col||' val from data order by 1'||chr(10);
    v_sql := v_sql ||'), x_values_rownum as ('||chr(10);
    v_sql := v_sql ||'  select rownum zeile, val from x_dist_values where rownum <= '||p_x_col_count||chr(10);
    v_sql := v_sql ||')'||chr(10);
  else
    v_sql := v_sql || chr(10);
  end if;
  v_sql := v_sql || 'select distinct '||chr(10);
  v_sql := v_sql || '  data.'||p_y_col||','||chr(10);
  for i in 1..p_x_col_count loop
    if p_cell_col_aggr is not null then
      v_sql := v_sql || p_cell_col_aggr||'(';
    end if;
    v_sql := v_sql || '  case when ';
    if p_x_col_type = 'VARCHAR2' then
      v_sql := v_sql || 'x.zeile = '||i;
    elsif p_x_col_type = 'NUMBER' then
      v_sql := v_sql || ' data.'||p_x_col||' >= '||p_x_col_start||
        ' + ('||(i - 1)||' * '||p_x_col_interval || ') ' ||
        ' and data.'||p_x_col||' < '||p_x_col_start||
        ' + ('||i||' * '||p_x_col_interval||') ';
    elsif p_x_col_type = 'DATE' then
      v_sql := v_sql || ' data.'||p_x_col||' >= '||p_x_col_start||
        ' + interval '''||((i - 1) * p_x_col_interval )||''' '||p_x_col_int_unit||
        ' and data.'||p_x_col||' < '||p_x_col_start||
        ' + interval '''||i * p_x_col_interval ||''' '||p_x_col_int_unit;
    end if;
    v_sql := v_sql ||' then '||p_cell_col|| ' else null end';
    if p_cell_col_aggr is not null then
      v_sql := v_sql || ')';
    end if;
    v_sql := v_sql || ' as "VALUE['||i||']"';
    if i < p_x_col_count then
      v_sql := v_sql || ',';
    end if;
    v_sql := v_sql ||chr(10);
  end loop;
  v_sql := v_sql || 'from data'||chr(10);
  if p_x_col_type = 'VARCHAR2' then
    v_sql := v_sql || 'join x_values_rownum x on (data.'||p_x_col||' = x.val)'||chr(10);
  end if;
  if p_cell_col_aggr is not null then
    v_sql := v_sql || 'group by data.'||p_y_col||chr(10);
  end if;
  return v_sql;
end;
/

Sie nutzen die Funktion wie folgt:

getPivotSql(
  P_SQL            => [SQL, dessen Ergebnismenge pivotiert werden soll]
  P_X_COL          => [X-Spalte]
  P_X_COL_TYPE     => [Datentyp der X-Spalte]
  P_X_COL_START    => [Bei NUMBER und DATE: Startwert der X-Spalte]
  P_X_COL_INTERVAL => [Bei NUMBER und DATE: Intervall, umwelches sich X mit jeder Spalte erhöhen soll]
  P_X_COL_INT_UNIT => [Bei DATE: Einheit des Intervalls (DAY, MONTH, YEAR)]
  P_X_COL_COUNT    => [Anzahl der X-Spalten]
  P_Y_COL          => [Y-Spalte]
  P_CELL_COL       => [Spalte, mit der die Zellen gefüllt werden sollen]
  P_CELL_COL_AGGR  => [Aggregatsfunktion für die Zellen]
)

Wagen Sie einen ersten Test im SQL Workshop (kodieren Sie hierfür das Startdatum hart auf den 23.01.2006):

select getPivotSql(
  'select u.produkt, datum, u.umsatz ' ||
  'from umsatz u '||
  'partition by (produkt) right outer join datum d using (datum) '||
  'where datum between to_date(''23.01.2006'', ''DD.MM.YYYY'') and to_date(''23.01.2006'', ''DD.MM.YYYY'') + 4',
  'DATUM',
  'DATE',       
  'to_date(''23.01.2006'', ''DD.MM.YYYY'')',
  1,
  'DAY',
  5,      
  'PRODUKT',
  'UMSATZ',
  'SUM' 
)
from dual

Das Ergebnis ist eine SQL-Abfrage, die zwar nicht so gut lesbar ist, die aber funktioniert:

with data as (
  select u.produkt, datum, u.umsatz 
  from umsatz u partition by (produkt) right outer join datum d using (datum) 
  where datum between to_date('23.01.2006', 'DD.MM.YYYY') and to_date('23.01.2006', 'DD.MM.YYYY') + 4
)
select distinct
  data.PRODUKT,
  SUM(  
    case 
      when  data.DATUM >= to_date('23.01.2006', 'DD.MM.YYYY') + interval '0' DAY 
        and data.DATUM < to_date('23.01.2006', 'DD.MM.YYYY') + interval '1' DAY 
      then UMSATZ 
      else null 
    end
  ) as "VALUE[1]",
  SUM(  
    case 
      when  data.DATUM >= to_date('23.01.2006', 'DD.MM.YYYY') + interval '1' DAY 
        and data.DATUM < to_date('23.01.2006', 'DD.MM.YYYY') + interval '2' DAY 
      then UMSATZ 
      else null 
    end
  ) as "VALUE[2]",
  :
  :
from data
group by data.PRODUKT

Wenn Sie die generierte Abfrage im SQL Workshop (oder in SQL*Plus) ausführen, werden Sie schon das gewünschte Ergebnis sehen. Nun geht es daran, die Kreuztabelle als Bericht auf die Anwendungsseite zu bringen. Erzeugen Sie einen neuen SQL Bericht und hinterlegen Sie den Aufruf von GETPIVOTSQL als SQL Abfrage:

begin
  return getPivotSql(
   'select u.produkt, datum, u.umsatz ' ||
   'from umsatz u '||
   'partition by (produkt) right outer join datum d using (datum) '||
   'where datum between to_date(:P1_START_DATUM, ''DD.MM.YYYY'') and to_date(:P1_START_DATUM, ''DD.MM.YYYY'') + 4',
   'DATUM',
   'DATE',       
   'to_date(:P1_START_DATUM, ''DD.MM.YYYY'')',
   1,
   'DAY',
   5,      
   'PRODUKT',
   'UMSATZ',
   'SUM' 
  );
end;

Das Schöne ist, dass APEX in der Berichtsdefinition auch eine PL/SQL-Funktion, die ein SQL zurückliefert, entgegennimmt - und die Funktion GETPIVOTSQL ist eine eben solche. Insofern setzen wir den Aufruf einfach hier ein.

Spaltenüberschriften einstellen

Sie sind fast fertig ... im Moment sieht das Ergebnis so aus:

Abb.5: Anwendungsseite mit den fast fertigen Kreuztabellen

Allerdings sind die Spaltenüberschriften TAG1 bis TAG5 bzw. VALUE[1] bis VALUE[5] noch nicht besonders aussagekräftig. Aber auch dies ist kein Problem, denn das "Beginn-Datum der Kreuztabelle ist durch das Element P1_START_DATUM variabel. Damit können auch die Spaltenüberschriften variabel gestaltet werden (Abbildung 5).

Abb.6: Spaltenüberschriften mit PL/SQL setzen

Klicken Sie in den Berichtsattributen beim Überschriftstyp PL/SQL an und hinterlegen Sie folgenden PL/SQL Block.

declare
 v_string varchar2(200);
begin
 v_string := 'Produkt:';
 for i in 0..4 loop
  v_string := v_string || to_char(to_date(:P1_START_DATUM,'DD.MM.YYYY') + i, 'DD.MM.YYYY');
  if i < 4 then 
   v_string := v_string || ':'; 
  end if;
 end loop;
 return v_string;
end;

Der PL/SQL Block muss die Überschriften durch Doppelpunkte getrennt zurückgeben. Die erste Spaltenüberschrift soll Produkt sein, die folgenden Spalten sollen das hochgezählte Datum erhalten - beginnend mit dem :P1_START_DATUM. Speichern Sie den PL/SQL-Block ab und starten Sie die Seite neu ...

Abb.7: Das Endergebnis: Die Kreuztabelle mit den "richtigen" Spaltenüberschriften

Damit ist die Kreuztabelle fertig. "Spielen" Sie ein wenig damit, indem Sie das :P1_START_DATUM ändern. Die Erstellung einer Schaltfläche zum Bewegen um eine Woche nach vorne oder zurück ist nun ein Leichtes (:P1_START_DATE - 7 bzw. :P1_START_DATE + 7). Das Element :P1_END_DATUM können Sie löschen - es wird nicht mehr benötigt.

Zurück zur Community-Seite