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 ...
