Saturday, September 13, 2008

Simulating print_table with grouping sets

Inspired by this OTN post from michaels, I realized I can also do the unpivot query from Simulating print_table with unpivot with just grouping sets functionality. So you won't have to wait until 11g, because grouping sets became available somewhere in the 9i range. The equivalent query is this:

rwijk@ORA11GR1> select case
2 when grouping(ename) = 0 then 'ENAME : ' || ename
3 when grouping(job) = 0 then 'JOB : ' || job
4 when grouping(mgr) = 0 then 'MGR : ' || to_char(mgr)
5 when grouping(hiredate) = 0
6 then 'HIREDATE: ' || to_char(hiredate,'dd-mm-yyyy')
7 when grouping(sal) = 0 then 'SAL : ' || to_char(sal)
8 when grouping(comm) = 0 then 'COMM : ' || to_char(comm)
9 when grouping(deptno) = 0 then 'DEPTNO : ' || to_char(deptno)
10 when grouping('-') = 0 then '======================'
11 else 'EMPNO : ' || to_char(empno)
12 end "Contents of table EMP"
13 from emp
14 group by grouping sets
15 ( (empno)
16 , (empno,ename)
17 , (empno,job)
18 , (empno,mgr)
19 , (empno,hiredate)
20 , (empno,sal)
21 , (empno,comm)
22 , (empno,deptno)
23 , (empno,'-')
24 )
25 order by empno
26 , case
27 when grouping(ename) = 0 then 2
28 when grouping(job) = 0 then 3
29 when grouping(mgr) = 0 then 4
30 when grouping(hiredate) = 0 then 5
31 when grouping(sal) = 0 then 6
32 when grouping(comm) = 0 then 7
33 when grouping(deptno) = 0 then 8
34 when grouping('-') = 0 then 9
35 else 1
36 end
37 /

Contents of table EMP
--------------------------------------------------
EMPNO : 7369
ENAME : SMITH
JOB : CLERK
MGR : 7902
HIREDATE: 17-12-1980
SAL : 800
COMM :
DEPTNO : 20
======================
EMPNO : 7499
ENAME : ALLEN
JOB : SALESMAN
MGR : 7698
HIREDATE: 20-02-1981
SAL : 1600
COMM : 300
DEPTNO : 30
======================
EMPNO : 7521
ENAME : WARD
JOB : SALESMAN
MGR : 7698
HIREDATE: 22-02-1981
SAL : 1250
COMM : 500
DEPTNO : 30
======================
EMPNO : 7566
ENAME : JONES
JOB : MANAGER
MGR : 7839
HIREDATE: 02-04-1981
SAL : 2975
COMM :
DEPTNO : 20
======================
EMPNO : 7654
ENAME : MARTIN
JOB : SALESMAN
MGR : 7698
HIREDATE: 28-09-1981
SAL : 1250
COMM : 1400
DEPTNO : 30
======================
EMPNO : 7698
ENAME : BLAKE
JOB : MANAGER
MGR : 7839
HIREDATE: 01-05-1981
SAL : 2850
COMM :
DEPTNO : 30
======================
EMPNO : 7782
ENAME : CLARK
JOB : MANAGER
MGR : 7839
HIREDATE: 09-06-1981
SAL : 2450
COMM :
DEPTNO : 10
======================
EMPNO : 7788
ENAME : SCOTT
JOB : ANALYST
MGR : 7566
HIREDATE: 19-04-1987
SAL : 3000
COMM :
DEPTNO : 20
======================
EMPNO : 7839
ENAME : KING
JOB : PRESIDENT
MGR :
HIREDATE: 17-11-1981
SAL : 5000
COMM :
DEPTNO : 10
======================
EMPNO : 7844
ENAME : TURNER
JOB : SALESMAN
MGR : 7698
HIREDATE: 08-09-1981
SAL : 1500
COMM : 0
DEPTNO : 30
======================
EMPNO : 7876
ENAME : ADAMS
JOB : CLERK
MGR : 7788
HIREDATE: 23-05-1987
SAL : 1100
COMM :
DEPTNO : 20
======================
EMPNO : 7900
ENAME : JAMES
JOB : CLERK
MGR : 7698
HIREDATE: 03-12-1981
SAL : 950
COMM :
DEPTNO : 30
======================
EMPNO : 7902
ENAME : FORD
JOB : ANALYST
MGR : 7566
HIREDATE: 03-12-1981
SAL : 3000
COMM :
DEPTNO : 20
======================
EMPNO : 7934
ENAME : MILLER
JOB : CLERK
MGR : 7782
HIREDATE: 23-01-1982
SAL : 1300
COMM :
DEPTNO : 10
======================

126 rows selected.

Practical use?

Err, again none ...

No comments:

Post a Comment