In article <38E5D97E.89B7FBE0_at_t-online.de>,
Edgar Rohr <ERohrPyro_at_t-online.de> wrote:
> Hi all,
>
> Could somebody send me a typical SQL-String to create a Crosstab-Query
> on a Oracle 7.3-DB
>
> Thx for your help!
>
scott_at_8i> REM First, we need to know the jobs we will pivot on --
> REM this just shows us what they are:
scott_at_8i>
scott_at_8i> select distinct job from emp;
JOB
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN
scott_at_8i>
scott_at_8i>
scott_at_8i> REM then we can see how to build a sparse matrix in one pass
scott_at_8i> REM that moves the sal column into the right virtual column
scott_at_8i>
scott_at_8i> select deptno, decode( job, 'ANALYST', sal, 0 ) Analyst,
2 decode( job, 'CLERK', sal, 0 ) Clerk,
3 decode( job, 'MANAGER', sal, 0 ) Manager,
4 decode( job, 'PRESIDENT', sal, 0 ) President,
5 decode( job, 'SALESMAN', sal, 0 ) Salesman
6 from emp
7 /
DEPTNO ANALYST CLERK MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ---------- ----------
20 0 800 0 0 0
30 0 0 0 0 1600
30 0 0 0 0 1250
20 0 0 2975 0 0
30 0 0 0 0 1250
30 0 0 2850 0 0
10 0 0 2450 0 0
20 3000 0 0 0 0
10 0 0 0 5000 0
30 0 0 0 0 1500
20 0 1100 0 0 0
30 0 950 0 0 0
20 3000 0 0 0 0
10 0 1300 0 0 0
14 rows selected.
scott_at_8i>
scott_at_8i> REM then we see how sum in this case and group by to 'squash'
scott_at_8i> REM the matrix by some grouping value
scott_at_8i>
scott_at_8i> select deptno, sum( decode( job, 'ANALYST', sal, 0 ) )
Analyst,
2 sum( decode( job, 'CLERK', sal, 0 ) ) Clerk,
3 sum( decode( job, 'MANAGER', sal, 0 ) ) Manager,
4 sum( decode( job, 'PRESIDENT', sal, 0 ) ) President,
5 sum( decode( job, 'SALESMAN', sal, 0 ) ) Salesman
6 from emp
7 group by deptno
8 /
DEPTNO ANALYST CLERK MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ---------- ----------
10 0 1300 2450 5000 0
20 6000 1900 2975 0 0
30 0 950 2850 0 5600
scott_at_8i>
--
Thomas Kyte tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Sat Apr 01 2000 - 07:09:16 CST
