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.

Kommentare:

Patrick Wolf hat gesagt…

Hallo Carsten,

Dein Beispiel hat mit ein wenig zum Nachdenken angeregt. Die einfache Loesung das Umkippen mit einem Decode/Case zu machen, wenn man die moeglichen Werte kennt wir den meisten ja bekannt sein.

SELECT deptno,
sum(decode(job, 'ANALYST', 1, 0)) AS ANALYST,
sum(decode(job, 'CLERK', 1, 0)) AS CLERK,
sum(decode(job, 'MANAGER', 1, 0)) AS MANAGER,
sum(decode(job, 'PRESIDENT', 1, 0)) AS PRESIDENT,
sum(decode(job, 'SALESMAN', 1, 0)) AS SALESMAN
FROM emp
group by deptno
ORDER BY deptno;

Aber bezogen auf Dein Beispiel, wenn die Werte "nicht bekannt" sind, dann geht das auch ohne die vielen Joins oder einer WITH clause.

SELECT deptno,
sum(decode(j.spalte, 1, 1, 0)) AS job_1,
sum(decode(j.spalte, 2, 1, 0)) AS job_2,
sum(decode(j.spalte, 3, 1, 0)) AS job_3,
sum(decode(j.spalte, 4, 1, 0)) AS job_4,
sum(decode(j.spalte, 5, 1, 0)) AS job_5
FROM ( SELECT ROWNUM AS spalte
, job
FROM ( SELECT DISTINCT job AS job FROM emp )
) j
, emp e
WHERE e.job = j.job
GROUP BY e.deptno
ORDER BY e.deptno;

Wuerde es fuer Jobs eine Lookup Tabelle geben, dann waere es noch leichter.

lg
Patrick
PS: Schade, dass die Kommentar Funktion kein PRE unterstuetzt, ist alles ein wenig unformatiert :-(

Carsten Czarski hat gesagt…

Hallo Patrick,

dieser Post ist eine "Vorbereitung" auf eine generische Lösung ... kommt im nächsten Post.

Und die generische Lösung soll auch DATE-Ausdrücke unterstützen können und mit einem beliebigen Select als Eingabedaten arbeiten können ... Wir können uns ja dann nochmal anschauen, ob das auch mit DECODE geht ...

Anonym hat gesagt…

Hallo !
Ich habe mich heute auch mit dem Thema beschäftigt und bin auf folgendes gestoßen:

http://www.oracle.com/global/de/pub/knowhow/dbspektrum_2.html

Dann im Abschnitt: Grouping set und cube-Klausel schauen.
Viele Grüße
Andreas

Anonym hat gesagt…

So ist es besser:

http://www.oracle.com/
global/de/pub/knowhow/
dbspektrum_2.html

Gruß
Andreas

Beliebte Postings