9. Februar 2009

Kreuztabellen (pivot tables) mal anders genutzt ...

English title: Creative usage of Pivot tables (matrix reports)

Kürzlich hatte ich eine recht interessante Frage von einem Kunden bekommen - ich hätte vorher zwar niemals gedacht, dass man sowas gebrauchen kann, aber danach ist mir klargeworden, dass alle Bus-, Zug- und Flugpläne typischerweise so aufgebaut werden: Wie kann man das Ergebnis einer SQL-Abfrage (eine Spalte) auf 10 Spalten verteilen? Also: Angenommen, man hätte folgende Ergebnismenge ...
Short time ago I was asked a question - I had never thought that someone really need this stuff, but then it got clear that every bus, train oder flight schedules are printed that way: How can the result of a SQL query (one column) be distributed over 10 (or say: X) columns?. Let's assume we have the following query result ...
TEXT
--------------------
17.12.80: SMITH
20.02.81: ALLEN
22.02.81: WARD
02.04.81: JONES
01.05.81: BLAKE
09.06.81: CLARK
08.09.81: TURNER
28.09.81: MARTIN
17.11.81: KING
03.12.81: FORD
03.12.81: JAMES
23.01.82: MILLER
09.12.82: SCOTT
12.01.83: ADAMS
... dann soll die gewissermaßen wie ein "Busfahrplan" aufbereitet werden:
... then we want this have returned in the manner of a bus schedule:
     ZEILE 1                    2                    3                    4                    5
---------- -------------------- -------------------- -------------------- -------------------- --------------------
         0 17.12.80: SMITH      20.02.81: ALLEN      22.02.81: WARD       02.04.81: JONES      01.05.81: BLAKE
         1 09.06.81: CLARK      08.09.81: TURNER     28.09.81: MARTIN     17.11.81: KING       03.12.81: FORD
         2 03.12.81: JAMES      23.01.82: MILLER     09.12.82: SCOTT      12.01.83: ADAMS
... und natürlich kann man das prozedural lösen (PL/SQL Prozeduren, Table Functions), aber eine SQL-Abfrage wäre besser - diese könnte man dann direkt in einem Reporting-Werkzeug oder als Grundlage für eine PDF-Ausgabe verwenden. Und da ich hier nun im Zug sitze, ist das die optimale Gelegenheit, ein Blog-Posting dazu zu schreiben.
And of course we can do this with procedural logic (PL/SQL- or Table Functions). But for now we want to achieve this with just a SQL query. And this has advantages. A pure SQL query can directly be used by reporting tools or PDF generators. So its an interesting question: And since I'm sitting in a train for the next two hours now this is a good opportunity to write a blog posting about it.
Man könnte sich dem Problem so nähern, dass man für jeden Wert erstmal eine Zeile und eine Spalte generiert. Das ist mit Divisions- und Modulo-Operationen auch ganz einfach: Der erste Schritt (für 5 Spalten) sähe also so aus:
An approach to the problem is to generate line and column numbers for our "matrix" report. This is an easy task - we have modulo and division operators in the SQL language. The first step (for five columns) then looks like this:
  SELECT 
    trunc((rownum - 1) / 5) as zeile,   
    mod(rownum, 5)          as spalte,
    hiredate || ': '||ename as text
  from (
    select ename, hiredate from emp order by hiredate
  )

     ZEILE     SPALTE TEXT
---------- ---------- --------------------
         0          1 17.12.80: SMITH
         0          2 20.02.81: ALLEN
         0          3 22.02.81: WARD
         0          4 02.04.81: JONES
         0          0 01.05.81: BLAKE
         1          1 09.06.81: CLARK
         1          2 08.09.81: TURNER
         1          3 28.09.81: MARTIN
         :          : :
Durch die Modulo-Operation hat die letzte Spalte die kleinste Nummer (0), aber das soll uns jetzt nicht weiter stören ... was jetzt noch übrig bleibt, ist das Verteilen der Werte auf die einzelnen Spalten - also eine Pivot-Operation. Und zum Glück gibt es in Oracle11g dafür die SQL Pivot Klausel:
Due to the modulo operation the last (fifth) column has the smallest number (zero) but this is so far no issue ... the rest of the problem is now a typical pivoting operation. And we're lucky: in Oracle11g there is the SQL PIVOT clause:
SELECT * FROM (
  SELECT 
    trunc((rownum - 1) / 5) as zeile,   
    mod(rownum, 5)          as spalte,
    hiredate || ': '||ename as text
  from (
    select ename, hiredate from emp order by hiredate
  )
) 
PIVOT (
  min(text) FOR spalte IN  (
    1 as "1", 
    2 as "2", 
    3 as "3", 
    4 as "4", 
    0 as "5"
  )
)
order by zeile
/
... was genau zum gewünschten Ergebnis führt ...
... which leads us exactly to the correct result ...
     ZEILE 1                    2                    3                    4                    5
---------- -------------------- -------------------- -------------------- -------------------- --------------------
         0 17.12.80: SMITH      20.02.81: ALLEN      22.02.81: WARD       02.04.81: JONES      01.05.81: BLAKE
         1 09.06.81: CLARK      08.09.81: TURNER     28.09.81: MARTIN     17.11.81: KING       03.12.81: FORD
         2 03.12.81: JAMES      23.01.82: MILLER     09.12.82: SCOTT      12.01.83: ADAMS
Mit Oracle11g ist das Problem also schon gelöst: Doch was, wenn das auf einer älteren Version laufen soll ...? Nun, zu Kreuztabellen (Pivot tables) vor Oracle11g hatte ich schonmal ein Blog Posting - die Funktion getPivotSql, die ich dort vorgestellt hatte, kann auch hier weiterhelfen. Zuerst erzeugen wir also mit getPivotSql das Kreuztabellen-SQL für obige Abfrage ...
So Oracle11g provides an easy solution for this problem. But what if we don't have Oracle11g. I had a blog posting about pivot tables or matrix reports in previous versions in the past. The PL/SQL function getPivotSql can help us here - it generates the final SQL query for us ...
select getpivotsql(
  'SELECT 
    trunc((rownum - 1) / 5) as zeile,   
    mod(rownum, 5)          as spalte,
    hiredate || '': ''||ename as text
  from (
    select ename, hiredate from emp order by hiredate
  )',
  'SPALTE',
  'VARCHAR2',
  null,
  null,
  null,
  5,
  'ZEILE',
  'TEXT',
  'MAX'
) from dual;

with data as (
  SELECT
    trunc((rownum - 1) / 5) as zeile,
    mod(rownum, 5)          as spalte,
    hiredate || ': '||ename as text
  from (
    select ename, hiredate from emp order by hiredate
  )
), x_dist_values as (
  select distinct SPALTE val from data order by 1
), x_values_rownum as (
  select rownum zeile, val from x_dist_values where rownum <= 5
)
select distinct
  data.ZEILE,
MAX(  case when x.zeile = 1 then TEXT else null end) as "VALUE[1]",
MAX(  case when x.zeile = 2 then TEXT else null end) as "VALUE[2]",
MAX(  case when x.zeile = 3 then TEXT else null end) as "VALUE[3]",
MAX(  case when x.zeile = 4 then TEXT else null end) as "VALUE[4]",
MAX(  case when x.zeile = 5 then TEXT else null end) as "VALUE[5]"
from data
join x_values_rownum x on (data.SPALTE = x.val)
group by data.ZEILE
Führt man dieses SQL aus, bekommt man ...
Executing this gives us ...
     ZEILE VALUE[1]             VALUE[2]             VALUE[3]             VALUE[4]             VALUE[5]
---------- -------------------- -------------------- -------------------- -------------------- --------------------
         1 03.12.81: FORD       09.06.81: CLARK      08.09.81: TURNER     28.09.81: MARTIN     17.11.81: KING
         2                      03.12.81: JAMES      23.01.82: MILLER     09.12.82: SCOTT      12.01.83: ADAMS
         0 01.05.81: BLAKE      17.12.80: SMITH      20.02.81: ALLEN      22.02.81: WARD       02.04.81: JONES
... schon nahe dran, aber noch nicht ganz korrekt - in der Tat muss man das SQL noch ein wenig nachbearbeiten. Erstmal nach Zeilen sortieren; dazu wird ein order by zeile ans Ende gehangen. Und die Spalten müssen auch anders sortiert werden - in der Oracle11g-Abfrage oben sieht man es ja: Die Spalte, die an letzter Stelle stehen soll, hat den Modulo "0" (Null). Dadurch stellt die Funktion getPivotSql sie an den Anfang.
... which is not exactly what we want - the resulting SQL query has to adjusted a little bit. The first thing is simple: Add an order by zeile at the end to have correct row order. The second issue is the column ordering. As seen above the columns are dermined by the modulo operation. And for the last column the modulo is zero (0). So the getPivotSql function places this column as the first one.
Also ändern wir das SQL ein diesen zwei Stellen um ...
So change the SQL query a little bit ...
with data as (
  SELECT
    trunc((rownum - 1) / 5) as zeile,
    mod(rownum, 5)          as spalte,
    hiredate || ': '||ename as text
  from (
    select ename, hiredate from emp order by hiredate
  )
), x_dist_values as (
  select distinct SPALTE val from data order by 1
), x_values_rownum as (
  select rownum zeile, val from x_dist_values where rownum <= 5
)
select distinct
  data.ZEILE,
MAX(  case when x.zeile = 2 then TEXT else null end) as "VALUE[1]",
MAX(  case when x.zeile = 3 then TEXT else null end) as "VALUE[2]",
MAX(  case when x.zeile = 4 then TEXT else null end) as "VALUE[3]",
MAX(  case when x.zeile = 5 then TEXT else null end) as "VALUE[4]",
MAX(  case when x.zeile = 1 then TEXT else null end) as "VALUE[5]"
from data
join x_values_rownum x on (data.SPALTE = x.val)
group by data.ZEILE
order by data.ZEILE
Und führt man das aus, kommt auch in Oracle9i oder Oracle10g das korrekte Ergebnis heraus.
... which leads us (also in Oracle9i or Oracle10g) to the result we wanted to achieve.
     ZEILE VALUE[1]             VALUE[2]             VALUE[3]             VALUE[4]             VALUE[5]
---------- -------------------- -------------------- -------------------- -------------------- --------------------
         0 17.12.80: SMITH      20.02.81: ALLEN      22.02.81: WARD       02.04.81: JONES      01.05.81: BLAKE
         1 09.06.81: CLARK      08.09.81: TURNER     28.09.81: MARTIN     17.11.81: KING       03.12.81: FORD
         2 03.12.81: JAMES      23.01.82: MILLER     09.12.82: SCOTT      12.01.83: ADAMS
Viel Spaß damit - vielleicht können es ja noch mehr gebrauchen ...
Perhaps more people find this useful - have fun!

Kommentare:

Anonym hat gesagt…

Hallo Herr Czarski,

eine sinnvolle Anwendung dessen, was Sie hier aufzeigen ist u.a. auch ein übersichtlich dargestellter Kalender mit z.B. Wochentagsspalten.

Aber auch bei PIVOT komme ich nicht umhin die Spaltenanzahl vorzugeben. Eine komplett dynamische Umformung, bei der ich aufgrund der Datenmenge vor dem Statement nicht sagen kann, wieviele Spalten es werden, geht nicht, oder? Z.B. wenn ich nur 4 von 5 Spalten bekomme, weil die fünfte keine Daten hat und damit gar nicht generiert werden soll...

Freundliche Grüße
Jürgen H. Moch

Carsten Czarski hat gesagt…

Hallo,

allein mit einem SQL SELECT und "Standardmitteln" geht das nicht; das liegt daran, dass jede SQL-Ergebnismenge (genau wie eine Tabelle oder View) eine fixe Anzahl von Spalten hat.

Möchte man die Spalten variabel gestalten, muss man ein wenig in die Trickkiste greifen. Es gibt eine spezielle Variante zum Erstellen von Table Functions, die in diesem und diesem Blog-Posting genutzt wurde. Die Umsetzung ist allerdings ein wenig aufwändiger ...

Viele Grüße

-Carsten

Beliebte Postings