Pivot query in oracle

Scenario 1:
CREATE TABLE t1 (
id NUMBER PRIMARY KEY,
sum1 NUMBER,
sum2 NUMBER,
sum3 NUMBER
);
INSERT INTO t1 VALUES (1,20,40,50);
INSERT INTO t1 VALUES (2,30,20,25);
INSERT INTO t1 VALUES (3,15,60,55);
COMMIT;
ID SUM1 SUM2 SUM3
---------- ---------- ---------- ----------
1 20 40 50
2 30 20 25
3 15 60 55
SELECT id, num, DECODE(num,'1',sum1,'2',sum2,'3',sum3) data
from t1,
(SELECT '1' num FROM dual UNION ALL
SELECT '2' num FROM dual UNION ALL
SELECT '3' num FROM dual )

ID NUM data
1 1
1 2
1 3
2 1
2 2
2 3



Scenario 2:
scott@DEV816> select job, deptno, count(*) 2 from emp 3 group by job, deptno 4 /JOB DEPTNO COUNT(*)--------- ---------- ----------ANALYST 20 2CLERK 10 1CLERK 20 2CLERK 30 1MANAGER 10 1MANAGER 20 1MANAGER 30 1PRESIDENT 10 1SALESMAN 30 49 rows selected.And you would like to make DEPTNO be a column. We have 4 deptno's in EMP, 10,20,30,40. We can make columns dept_10, dept_20, dept_30, dept_40 that have the values that are currently in the count column. It would look like this:scott@DEV816> scott@DEV816> select job, 2 max( decode( deptno, 10, cnt, null ) ) dept_10, 3 max( decode( deptno, 20, cnt, null ) ) dept_20, 4 max( decode( deptno, 30, cnt, null ) ) dept_30, 5 max( decode( deptno, 40, cnt, null ) ) dept_40 6 from ( select job, deptno, count(*) cnt 7 from emp 8 group by job, deptno ) 9 group by job 10 /JOB DEPT_10 DEPT_20 DEPT_30 DEPT_40--------- ---------- ---------- ---------- ----------ANALYST 2CLERK 1 2 1MANAGER 1 1 1PRESIDENT 1SALESMAN 4