Oracle11g bytes row-to-column Conversion

Oracle11g bytes row-to-column Conversion

I have written a line-to-column article: Oracle simple column-to-row

SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE 11.2.0.1.0 ProductionTNS for Linux: Version 11.2.0.1.0 - ProductionNLSRTL Version 11.2.0.1.0 - Production

The requirement is to count the total salaries of different jobs in each department.

SQL> select deptno,ename,job,sal from emp;DEPTNO ENAME JOB SAL---------- ---------- --------- ----------20 SMITH CLERK 80030 ALLEN SALESMAN 160030 WARD SALESMAN 125020 JONES MANAGER 297530 MARTIN SALESMAN 125030 BLAKE MANAGER 285010 CLARK MANAGER 245020 SCOTT ANALYST 300010 KING PRESIDENT 500030 TURNER SALESMAN 150020 ADAMS CLERK 110030 JAMES CLERK 95020 FORD ANALYST 300010 MILLER CLERK 1300

14 rows have been selected.

SQL> select deptno,nvl(sum(decode(job, 'MANAGER', sal)), 0) "s_MANAGER",nvl(sum(decode(job, 'ANALYST', sal)), 0) "s_ANALYST",nvl(sum(decode(job, 'CLERK', sal)), 0) "s_CLERK",nvl(sum(decode(job, 'PRESIDENT', sal)), 0) "s_PRESIDENT",nvl(sum(decode(job, 'SALESMAN', sal)), 0) "s_SALESMAN"from empgroup by deptno;DEPTNO s_MANAGER s_ANALYST s_CLERK s_PRESIDENT s_SALESMAN---------- ---------- ---------- ---------- ----------- ----------30 2850 0 950 0 560020 2975 6000 1900 0 010 2450 0 1300 5000 0

It will be simpler to use tokens.

SQL> with p as (select deptno,job,sal from emp)SELECT * FROM p pivot ( SUM(sal)FOR job IN ('MANAGER' as "s_MANAGER",'ANALYST' as "s_ANALYST",'CLERK' as "s_CLERK",'PRESIDENT' as "s_PRESIDENT",'SALESMAN' as "s_SALESMAN" ));DEPTNO s_MANAGER s_ANALYST s_CLERK s_PRESIDENT s_SALESMAN---------- ---------- ---------- ---------- ----------- ----------30 2850 950 560020 2975 6000 190010 2450 1300 5000