2. April 2007

Kreuztabelle -I-

Häufig kommt die Anforderung, Tabellendaten als Kreuztabelle darzustellen. Ausgangspunkt ist eine "normale" SQL-Abfrage ...

select deptno, job, count(ename) anzahl 
from emp 
group by deptno, job 
order by deptno, job;

    DEPTNO JOB           ANZAHL
---------- --------- ----------
        10 CLERK              1
        10 MANAGER            1
        10 PRESIDENT          1
        20 ANALYST            2
        20 CLERK              2
        20 MANAGER            1
        30 CLERK              1
        30 MANAGER            1
        30 SALESMAN           4

Möchte man die Tabelle nun "kippen" ...

    DEPTNO     JOB_1      JOB_2      JOB_3     JOB_4      JOB_5
---------- --------- ---------- ---------- --------- ----------
        10                    1          1         1
        20         2          2          1
        30                    1          1                    4

... muss man die Abfrage für jede Spalte wie folgt mit sich selbst joinen. Wenn die "Grundabfrage" keine einfache Tabelle ist, kann man mit der WITH ... AS-Klausel auch eine SQL-Abfrage als Inline-View definieren.

with data as (
  select deptno, job, count(ename) anzahl from emp group by deptno, job

 deptno
), x_dist_values as (
  select distinct JOB val from data order by 1
), x_values_rownum as (
  select rownum zeile, val from x_dist_values where rownum <= 5
)
select distinct
  data.DEPTNO,
  p1.ANZAHL "VALUE[1]",
  p2.ANZAHL "VALUE[2]",
  p3.ANZAHL "VALUE[3]",
  p4.ANZAHL "VALUE[4]",
  p5.ANZAHL "VALUE[5]"
from data
join (select val from x_values_rownum where zeile = 1) xvr1 on (1=1)
join (select val from x_values_rownum where zeile = 2) xvr2 on (1=1)
join (select val from x_values_rownum where zeile = 3) xvr3 on (1=1)
join (select val from x_values_rownum where zeile = 4) xvr4 on (1=1)
join (select val from x_values_rownum where zeile = 5) xvr5 on (1=1)
left outer join data p1 on (
  data.deptno = p1.deptno and
  p1.JOB = xvr1.val
)
left outer join data p2 on (
  data.deptno = p2.deptno and
  p2.JOB = xvr2.val
)
left outer join data p3 on (
  data.deptno = p3.deptno and
  p3.JOB = xvr3.val
)
left outer join data p4 on (
  data.deptno = p4.deptno and
  p4.JOB = xvr4.val
)
left outer join data p5 on (
  data.deptno = p5.deptno and
  p5.JOB = xvr5.val
)

Das Prinzip einer relationalen Datenbank und damit eines jeden SQL ist, dass die Spalten stets fix sind. Ein SQL-Kommando, welches stets eine dynamische Anzahl von Spalten anzeigt, ist nicht so einfach. Lösbar ist das Problem jedoch mit dynamischem SQL. Wie das geht, steht dann in der nächsten Ausgabe.

Beliebte Postings