Live ORACLE

rem -----------------------------------------------------------------------

rem UPDATED version

rem Filename:   matrix.SQL

rem Purpose:    Example of a CROSS MATRIX report implemented USING

rem             standard SQL.

rem Date:       12-Feb-2000

rem Author:     Frank Naude, Oracle faq

rem

rem Updated By  Mahesh Pednekar. (bunty609@hotmail.com)

rem Description Removed the Main query because the sub query itself

rem             will full fill the requirement.

rem -----------------------------------------------------------------------


SELECT   job,

        Sum(Decode(deptno,10,sal)) dept10,

        Sum(Decode(deptno,20,sal)) dept20,

        Sum(Decode(deptno,30,sal)) dept30,

        Sum(Decode(deptno,40,sal)) dept40

FROM     scott.emp

GROUP BY job

/

-- Sample output:

--

-- JOB           DEPT10     DEPT20     DEPT30     DEPT40

-- --------- ---------- ---------- ---------- ----------

-- ANALYST                    6000

-- CLERK           1300       1900        950

-- MANAGER         2450       2975       2850

-- PRESIDENT       5000

-- SALESMAN                              5600

--

CROSS MATRIX report