Pivot Query
[php]
A pivot query is when you want to take some data such as:
C1C2C3
----- ----- ------
a1b1x1
a1b1x2
a1b1x3
…
and you would like to display in the following format:
C1C2C3(1) C3(2)C3(3)
----- ----- ---------------
a1b1x1x2 x3
…
This turns rows into columns. For example taking the distinct jobs within a department and making them be columns so the output would look like:
DEPTNO JOB_1 JOB_2 JOB_3
---------- --------- --------- ---------
10 CLERK MANAGER PRESIDENT
20 ANALYST ANALYST CLERK
30 CLERK MANAGER SALESMAN
instead of this:
DEPTNO JOB
---------- ---------
10 CLERK
10 MANAGER
10 PRESIDENT
20 ANALYST
20 CLERK
20 MANAGER
30 CLERK
30 MANAGER
30 SALESMAN
I'm going to show two examples for pivots. The first will be another implementation of the preceding question. The second shows how to pivot any resultset in a generic fashion and gives you a template for doing so.
In the first case, let's say you wanted to show the top 3 salary earners in each department as columns. The query needs to return exactly 1 row per department and the row would have 4 columns; the DEPTNO, the name of the highest paid employee in the department, the name of the next highest paid, and so on. Using this new functionality - this is almost easy (before these functions - this was virtually impossible):
ops$tkyte@DEV816> select deptno,
2
max(decode(seq,1,ename,null)) highest_paid,
3
max(decode(seq,2,ename,null)) second_highest,
4
max(decode(seq,3,ename,null)) third_highest
5from (SELECT deptno, ename,
6
row_number() OVER
7
(PARTITION BY deptno
8
ORDER BY sal desc NULLS LAST) seq
9
FROM emp)
10where seqselect deptno,
2max(decode(seq,1,ename,null)) highest_paid,
3max(decode(seq,2,ename,null)) second_highest,
4max(decode(seq,3,ename,null)) third_highest
5from (select deptno, ename,
6row_number() over
7
(partition by deptno
8 order by sal desc nulls last)
9seq from emp)
10where seqselect max(count(*)) from emp group by deptno, job;
MAX(COUNT(*))
-------------
4
This tells us the number of columns; now we can generate the query:
scott@TKYTE816> select deptno, job,
2max(decode(rn, 1, ename, null)) ename_1,
3max(decode(rn, 1, sal, null)) sal_1,
4max(decode(rn, 2, ename, null)) ename_2,
5max(decode(rn, 2, sal, null)) sal_2,
6max(decode(rn, 3, ename, null)) ename_3,
7max(decode(rn, 3, sal, null)) sal_3,
8max(decode(rn, 4, ename, null)) ename_4,
9max(decode(rn, 4, sal, null)) sal_4
10from (select deptno, job, ename, sal,
11row_number() over (partition by deptno, job
12
order by sal, ename)
13rn from emp)
14group by deptno, job
15/
DEPTNO JOB ENAME_1 SAL_1 ENAME_2 SAL_2 ENAME_3SAL_3 ENAME_ SAL_4
------ --------- ----------- --------- ----- ---------- ----- ------ -----
10 CLERK MILLER 1300
10 MANAGER CLARK2450
10 PRESIDENT KING 5000
20 ANALYST FORD 3000 SCOTT3000
20 CLERK SMITH 800 ADAMS1100
20 MANAGER JONES2975
30 CLERK JAMES99
30 MANAGER BLAKE99
30 SALESMANALLEN99 MARTIN 99 TURNER99 WARD99
9 rows selected.
We inserted values of 99 into the salary column of all the employees in department 30, earlier in the chapter. To pivot a resultset, we can generalize further. If you have a set of columns C1, C2, C3, ... CN and you want to keep columns C1 ... Cx cross record and Cx+1 ... CN in record, the syntax of the query would be:
Select C1, C2, ... CX,
max(decode(rn,1,C{X+1},null)) cx+1_1,...max(decode(rn,1,CN,null)) CN_1
max(decode(rn,2,C{X+1},null)) cx+1_2,...max(decode(rn,1,CN,null)) CN_2
...
max(decode(rn,N,c{X+1},null)) cx+1_N,...max(decode(rn,1,CN,null)) CN_N
from (select C1, C2, ... CN
row_number() over (partition by C1, C2, ... CX
order bycreate or replace package my_pkg
2as
3type refcursor is ref cursor;
4type array is table of varchar2(30);
5procedure pivot(p_max_cols in number default NULL,
6
p_max_cols_query in varchar2 default NULL,
7
p_query
in varchar2,
8
p_anchor in array,
9
p_pivot
in array,
10
p_cursor in out refcursor);
12end;
Package created.
Here, you must input values for either P_MAX_COLS or P_MAX_COLS_QUERY. SQL needs to know the number of columns in a query and this parameter will allow us to build a query with the proper number of columns. The value you should send in here will be the output of a query similar to:
scott@TKYTE816> select max(count(*)) from emp group by deptno, job;
This is the count of the discrete values that are currently in rows, which we will put into columns. You can either use a query to obtain this number, or insert the number yourself if you already know it.
The P_QUERY parameter is simply the query that gathers your data together. Using the example shown earlier the query would be:
10from (select deptno, job, ename, sal,
11row_number() over (partition by deptno, job
12
order by sal, ename)
13rn from emp)
The next two inputs are arrays of column names. The P_ANCHOR tells us what columns will stay cross record (down the page) and P_PIVOT states the columns that will go in record (across the page). In our example from above, P_ANCHOR = ('DEPTNO', 'JOB') and P_PIVOT = ('ENAME','SAL'). Skipping over the implementation for a moment, the entire call put together might look like this:
scott@TKYTE816> variable x refcursor
scott@TKYTE816> set autoprint on
scott@TKYTE816> begin
2my_pkg.pivot
3(p_max_cols_query => 'select max(count(*)) from emp
4
group by deptno,job',
5 p_query => 'select deptno, job, ename, sal,
6 row_number() over (partition by deptno, job
7
order by sal, ename)
8 rn from emp a',
9
10 p_anchor => my_pkg.array('DEPTNO','JOB'),
11 p_pivot=> my_pkg.array('ENAME', 'SAL'),
12 p_cursor => :x);
13end;
PL/SQL procedure successfully completed.
DEPTNO JOB ENAME_ SAL_1 ENAME_2SAL_2 ENAME_3SAL_3 ENAME_ SAL_4
------ --------- ------ ----- ---------- ----- ---------- ----- ------ -----
10 CLERK MILLER1300
10 MANAGER CLARK 2450
10 PRESIDENT KING5000
20 ANALYST FORD3000 SCOTT 3000
20 CLERK SMITH800 ADAMS 1100
20 MANAGER JONES 2975
30 CLERK JAMES 99
30 MANAGER BLAKE 99
30 SALESMANALLEN 99 MARTIN99 TURNER99 WARD99
9 rows selected.
As you can see, that dynamically rewrote our query using the generalized template we developed. The implementation of the package body is straightforward:
scott@TKYTE816> create or replace package body my_pkg
2as
3
4procedure pivot(p_max_cols in number default null,
5
p_max_cols_query in varchar2 default null,
6
p_query
in varchar2,
7
p_anchor in array,
8
p_pivot
in array,
9
p_cursor in out refcursor)
10as
11l_max_cols number;
12l_querylong;
13l_cnames array;
14begin
15-- figure out the number of columns we must support
16-- we either KNOW this or we have a query that can tell us
17if (p_max_cols is not null)
18then
19
l_max_cols := p_max_cols;
20elsif (p_max_cols_query is not null)
21then
22
execute immediate p_max_cols_query into l_max_cols;
23else
24
raise_application_error(-20001, 'Cannot figure out max cols');
25end if;
26
27
28-- Now, construct the query that can answer the question for us...
29-- start with the C1, C2, ... CX columns:
30
31l_query := 'select ';
32for i in 1 .. p_anchor.count
33
34loop
35l_query := l_query || p_anchor(i) || ',';
36end loop;
37
38-- Now add in the C{x+1}... CN columns to be pivoted:
39-- the format is "max(decode(rn,1,C{X+1},null)) cx+1_1"
40
41for i in 1 .. l_max_cols
42loop
43for j in 1 .. p_pivot.count
44loop
45
l_query := l_query ||
46
'max(decode(rn,'||i||','||
47
p_pivot(j)||',null)) ' ||
48
p_pivot(j) || '_' || i || ',';
49end loop;
50end loop;
51
52-- Now just add in the original query
53
54l_query := rtrim(l_query,',') || ' from (' || p_query || ') group by ';
55
56-- and then the group by columns...
57
58for i in 1 .. p_anchor.count
59loop
60l_query := l_query || p_anchor(i) || ',';
61end loop;
62l_query := rtrim(l_query,',');
63
64-- and return it
65
66execute immediate 'alter session set cursor_sharing=force';
67open p_cursor for l_query;
68execute immediate 'alter session set cursor_sharing=exact';
69end;
70
71end;
72/
Package body created.
It only does a little string manipulation to rewrite the query and open a REF CURSOR dynamically. In the likely event the query had a predicate with constants and such in it, we set cursor sharing on and then back off for the parse of this query to facilitate bind variables (see the section on tuning for more information on that). Now we have a fully parsed query that is ready to be fetched from.
---------
[/php]
|
