One of the frequently asked questions on SQL forums is how to present data “horizontally” rather than “vertically”, known as a “pivot query”. For example, instead of this:
DEPTNO JOB HEADCOUNT
---------- --------- ----------
10 CLERK 1
10 MANAGER 1
20 ANALYST 2
20 CLERK 2
20 MANAGER 1
30 CLERK 1
30 MANAGER 1
30 SALESMAN 4
… people would like to know how to produce this:
DEPTNO ANALYST CLERK MANAGER SALESMAN
---------- ---------- ---------- ---------- ----------
10 1 1
20 2 2 1
30 1 1 4
In Oracle, the normal way is to write a query using DECODE like this:
select deptno
, count(DECODE(job,'ANALYST', 1)) as "ANALYST"
, count(DECODE(job,'CLERK', 1)) as "CLERK"
, count(DECODE(job,'MANAGER', 1)) as "MANAGER"
, count(DECODE(job,'SALESMAN', 1)) as "SALESMAN"
from emp
group by deptno
order by deptno;
The DECODE ensures that the jobs go in the right columns.
Now this type of query is quite easy to write (when you know how), but is also quite laborious; also, if the list of jobs changes then the query needs to be changed too.
To make producing such queries a “piece of cake” I have built a package called pivot that can be used to generate the SQL like this:
begin
pivot.print_pivot_query
( 'deptno'
, 'job'
, 'emp'
, '1'
, agg_types => 'count'
);
end;
/
select deptno
, count(DECODE(job,'ANALYST', 1)) as "ANALYST"
, count(DECODE(job,'CLERK', 1)) as "CLERK"
, count(DECODE(job,'MANAGER', 1)) as "MANAGER"
, count(DECODE(job,'SALESMAN', 1)) as "SALESMAN"
from emp
group by deptno
order by deptno
PL/SQL procedure successfully completed.
The package code follows. Note that it requires the “parse” package that I posted earlier.
CREATE OR REPLACE PACKAGE pivot IS
TYPE ref_cursor IS REF CURSOR;
PROCEDURE print_pivot_query
( group_cols IN VARCHAR2 -- Comma-separated list of column(s), including table alias if applicable,
-- to be used to group the records
, pivot_col IN VARCHAR2 -- The name of the column to be pivoted, including table alias if applicable
, tables IN VARCHAR2 -- Comma-separated list of table(s), with table alias if applicable
, value_cols IN VARCHAR2 -- Comma-separated list of column(s), including table alias if applicable
-- for which aggregates are to be shown at each intersection
, pivot_values IN VARCHAR2 DEFAULT NULL -- Comma-separated list of values of pivot column to be used;
-- if omitted, all values are used (determined dynamically)
, agg_types IN VARCHAR2 DEFAULT NULL -- Comma-separated list of aggregate types, corresponding to value_cols;
-- if omitted, SUM is the default
, where_clause IN VARCHAR2 DEFAULT NULL -- where clause of query
, order_by IN VARCHAR2 DEFAULT NULL -- order by clause; if omitted, output is ordered by group_cols
);
FUNCTION pivot_query
( group_cols IN VARCHAR2
, pivot_col IN VARCHAR2
, tables IN VARCHAR2
, value_cols IN VARCHAR2
, pivot_values IN VARCHAR2 DEFAULT NULL
, agg_types IN VARCHAR2 DEFAULT NULL
, where_clause IN VARCHAR2 DEFAULT NULL
, order_by IN VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2;
FUNCTION pivot_cursor
( group_cols IN VARCHAR2
, pivot_col IN VARCHAR2
, tables IN VARCHAR2
, value_cols IN VARCHAR2
, pivot_values IN VARCHAR2 DEFAULT NULL
, agg_types IN VARCHAR2 DEFAULT NULL
, where_clause IN VARCHAR2 DEFAULT NULL
, order_by IN VARCHAR2 DEFAULT NULL
) RETURN ref_cursor;
END;
/
CREATE OR REPLACE PACKAGE BODY pivot IS
g_mode varchar2(10);
g_sql varchar2(32767);
PROCEDURE pr
( p_text in varchar2
)
IS
v_text VARCHAR2(32767) := p_text;
BEGIN
if g_mode = 'PRINT' then
WHILE LENGTH(v_text) > 255 LOOP
DBMS_OUTPUT.PUT_LINE( SUBSTR(v_text,1,255) );
v_text := SUBSTR(v_text,256);
END LOOP;
DBMS_OUTPUT.PUT_LINE( v_text );
else
g_sql := g_sql || ' ' || p_text;
end if;
END pr;
/*
|| Generates the SQL statement for a pivot query based on parameters
|| Example:
|| create_pivot_query
|| ( group_cols => 'd.dname'
|| , pivot_col => 'e.job'
|| , tables => 'emp e, dept d'
|| , value_cols => 'e.sal,e.age'
|| , agg_types => 'min,max'
|| , where_clause => 'e.deptno = d.deptno'
|| );
|| Generates a query like:
|| select d.dname
|| , min(DECODE(e.job,'ANALYST', e.sal, 0 )) as "min_ANALYST_esal"
|| , max(DECODE(e.job,'ANALYST', e.age, 0 )) as "max_ANALYST_eage"
|| , min(DECODE(e.job,'CLERK', e.sal, 0 )) as "min_CLERK_esal"
|| , max(DECODE(e.job,'CLERK', e.age, 0 )) as "max_CLERK_eage"
|| , min(DECODE(e.job,'MANAGER', e.sal, 0 )) as "min_MANAGER_esal"
|| , max(DECODE(e.job,'MANAGER', e.age, 0 )) as "max_MANAGER_eage"
|| , min(DECODE(e.job,'PRESIDENT', e.sal, 0 )) as "min_PRESIDENT_esal"
|| , max(DECODE(e.job,'PRESIDENT', e.age, 0 )) as "max_PRESIDENT_eage"
|| , min(DECODE(e.job,'SALESMAN', e.sal, 0 )) as "min_SALESMAN_esal"
|| , max(DECODE(e.job,'SALESMAN', e.age, 0 )) as "max_SALESMAN_eage"
|| from emp e, dept d
|| where e.deptno = d.deptno
|| group by d.dname
|| order by d.dname
||
|| i.e. the parameters are used like this:
|| select
|| , (DECODE(,, , 0 ))
|| from
|| where
|| group by
|| order by
||
*/
PROCEDURE define_pivot_query
( group_cols IN VARCHAR2
, pivot_col IN VARCHAR2
, tables IN VARCHAR2
, value_cols IN VARCHAR2
, pivot_values IN VARCHAR2 DEFAULT NULL
, agg_types IN VARCHAR2 DEFAULT NULL
, where_clause IN VARCHAR2 DEFAULT NULL
, order_by IN VARCHAR2 DEFAULT NULL
)
IS
type ref_cursor is ref cursor;
rc ref_cursor;
pv_tab parse.varchar2_table;
val_tab parse.varchar2_table;
agg_tab parse.varchar2_table;
num_pvs integer := 0;
num_vals integer := 0;
num_aggs integer := 0;
alias varchar2(100);
BEGIN
g_sql := NULL;
-- Determine pivot values: use list if given, otherwise construct query to find them
if pivot_values is not null then
parse.delimstring_to_table( pivot_values, pv_tab, num_pvs );
else
open rc for 'select distinct ' || pivot_col || ' from ' || tables || ' where ' || nvl(where_clause,'1=1');
loop
num_pvs := num_pvs+1;
fetch rc into pv_tab(num_pvs);
exit when rc%notfound;
end loop;
close rc;
num_pvs := num_pvs-1;
end if;
parse.delimstring_to_table( value_cols, val_tab, num_vals );
-- Determine aggregate functions (default is SUM)
if agg_types is not null then
parse.delimstring_to_table( agg_types, agg_tab, num_aggs );
end if;
if num_aggs <> num_vals then
for i in num_aggs+1..num_vals loop
agg_tab(i) := 'sum';
end loop;
end if;
pr('select '||group_cols);
for pv in 1..num_pvs loop
pv_tab(pv) := trim(pv_tab(pv));
for val in 1..num_vals loop
val_tab(val) := trim(val_tab(val));
if num_vals = 1 then
alias := substr(pv_tab(pv),1,30);
else
alias := substr(agg_tab(val) || '_' || TRANSLATE(pv_tab(pv),'x. -()<>','x') || '_' || TRANSLATE(val_tab(val),'x. -()<>','x'),1,30);
end if;
pr( ', '||agg_tab(val)||'(DECODE(' || pivot_col || ',''' || pv_tab(pv) || ''', ' || val_tab(val) || '))'
|| ' as "' || alias || '"' );
end loop;
end loop;
pr('from ' || tables );
if where_clause is not null then
pr('where ' || where_clause);
end if;
pr('group by ' || group_cols);
pr('order by ' || NVL(order_by,group_cols));
END define_pivot_query;
PROCEDURE print_pivot_query
( group_cols IN VARCHAR2
, pivot_col IN VARCHAR2
, tables IN VARCHAR2
, value_cols IN VARCHAR2
, pivot_values IN VARCHAR2 DEFAULT NULL
, agg_types IN VARCHAR2 DEFAULT NULL
, where_clause IN VARCHAR2 DEFAULT NULL
, order_by IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
g_mode := 'PRINT';
define_pivot_query
( group_cols
, pivot_col
, tables
, value_cols
, pivot_values
, agg_types
, where_clause
, order_by
);
END;
FUNCTION pivot_query
( group_cols IN VARCHAR2
, pivot_col IN VARCHAR2
, tables IN VARCHAR2
, value_cols IN VARCHAR2
, pivot_values IN VARCHAR2 DEFAULT NULL
, agg_types IN VARCHAR2 DEFAULT NULL
, where_clause IN VARCHAR2 DEFAULT NULL
, order_by IN VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2
IS
BEGIN
g_mode := 'TEXT';
define_pivot_query
( group_cols
, pivot_col
, tables
, value_cols
, pivot_values
, agg_types
, where_clause
, order_by
);
RETURN g_sql;
END;
FUNCTION pivot_cursor
( group_cols IN VARCHAR2
, pivot_col IN VARCHAR2
, tables IN VARCHAR2
, value_cols IN VARCHAR2
, pivot_values IN VARCHAR2 DEFAULT NULL
, agg_types IN VARCHAR2 DEFAULT NULL
, where_clause IN VARCHAR2 DEFAULT NULL
, order_by IN VARCHAR2 DEFAULT NULL
) RETURN ref_cursor
IS
rc ref_cursor;
BEGIN
g_mode := 'CURSOR';
define_pivot_query
( group_cols
, pivot_col
, tables
, value_cols
, pivot_values
, agg_types
, where_clause
, order_by
);
OPEN rc FOR g_sql;
RETURN rc;
END;
END;
/
Showing posts with label pivot. Show all posts
Showing posts with label pivot. Show all posts
Friday, October 15, 2004
Subscribe to:
Posts (Atom)