Kreuztabellen: Jetzt wird es generisch!

Nun kommt der zweite Teil. Nach Patricks Kommentar habe ich die Lösung nochmals durchdacht und festgestellt, dass die DECODE-Variante gar nicht so schlecht ist (vor allem erzeugt sie bessere Ausführungspläne) - Vielen Dank, Patrick. Insofern habe ich die generische Lösung umgestellt ... bis auf zwei Dinge:

• Die WITH-Klausel bleibt drin - sie erlaubt mir, das "Pivoting" mit beliebigen Abfragen durchzuführen
• Anstelle von DECODE verwende ich CASE WHEN ... END-Ausdrücke - diese sind mächtiger als DECODE

Daraus ergibt sich folgende Funktion:

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 wird wie folgt aufgerufen:

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]
)

Ein Beispiel: Auf der X-Achse sollen die Jobs, auf der Y-Achse die Departments aufgetragen werden; in den Zellen soll die Gehaltssumme stehen, so dass man sehen kann, in welchen Jobs und in welchen Abteilungen welche Gehälter gezahlt werden.

select getPivotSql(
  'select deptno, job, sal from emp', /* SQL mit den Daten                         */
  'JOB',                              /* X-Spalte                                  */
  'VARCHAR2',                         /* Datentyp der X-Spalte                     */
  null,                               /* VARCHAR2: Kein Startwert                  */
  null,                               /* VARCHAR2: Kein Intervall                  */
  null,                               /* VARCHAR2: Keine Einheit für das Intervall */
  5,                                  /* maximal 5 X-Spalten                       */
  'DEPTNO',                           /* Y-Achse (Zeilen)                          */
  'SAL',                              /* Zellenwerte (Gehalt)                      */
  'SUM'                               /* Aggregatsfunktion (Summe)                 */
)
from dual
/

Als Ergebnis kommt folgendes SQL heraus ...

with data as (
  select deptno, job, sal from emp
), 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,
SUM(  case when x.zeile = 1 then SAL else null end) as "VALUE[1]",
SUM(  case when x.zeile = 2 then SAL else null end) as "VALUE[2]",
SUM(  case when x.zeile = 3 then SAL else null end) as "VALUE[3]",
SUM(  case when x.zeile = 4 then SAL else null end) as "VALUE[4]",
SUM(  case when x.zeile = 5 then SAL else null end) as "VALUE[5]"
from data
join x_values_rownum x on (data.JOB = x.val)
group by data.DEPTNO

... und ausgeführt ergibt sich:

    DEPTNO   VALUE[1]   VALUE[2]   VALUE[3]   VALUE[4]   VALUE[5]
---------- ---------- ---------- ---------- ---------- ----------
        30                   950       2850                  5600
        20       6000       1900       2975
        10                  1300       2450       5000

3 Zeilen ausgewählt.

Andere Beispiele: Es soll anhand des Gehalts selektiert werden. Ausgehend von einem Startgehalt von 1.000 sollen drei Gruppen gebildet werden (1000 - 2500, 2500 - 4000, 4000 - 5500). Diese werden auf der X-Achse aufgetragen - Die Y-Achse bilden diesmal die Jobs:

select getPivotSql(
  'select job, sal, empno from emp',  /* SQL mit den Daten                         */
  'SAL',                              /* X-Spalte                                  */
  'NUMBER',                           /* Datentyp der X-Spalte                     */
  1000,                               /* VARCHAR2: Kein Startwert                  */
  1500,                               /* VARCHAR2: Kein Intervall                  */
  null,                               /* VARCHAR2: Keine Einheit für das Intervall */
  3,                                  /* maximal 5 X-Spalten                       */
  'JOB',                              /* Y-Achse (Zeilen)                          */
  'EMPNO',                            /* Zellenwerte (Gehalt)                      */
  'COUNT'                             /* Aggregatsfunktion (Summe)                 */
)
from dual
/

Dann kommt dieses SQL heraus:

with data as (
  select job, sal, empno from emp
)
select distinct
  data.JOB,
  COUNT(  case when  data.SAL between 1000 + (0 * 1500) and 1000 + (1 * 1500)  then EMPNO else null end) as "VALUE[1]",
  COUNT(  case when  data.SAL between 1000 + (1 * 1500) and 1000 + (2 * 1500)  then EMPNO else null end) as "VALUE[2]",
  COUNT(  case when  data.SAL between 1000 + (2 * 1500) and 1000 + (3 * 1500)  then EMPNO else null end) as "VALUE[3]"
from data
group by data.JOB

Ausgeführt ...

JOB         VALUE[1]   VALUE[2]   VALUE[3]
--------- ---------- ---------- ----------
CLERK              2          0          0
SALESMAN           4          0          0
PRESIDENT          0          0          1
MANAGER            1          2          0
ANALYST            0          2          0

Probiert es aus! Feedback bzw. Kommentare sind natürlich stets erwünscht ...