Felipe Vidal de Oliveira Duarte

PL-SQL/ORACLE: Transformar Linhas para Coluna

setembro 30, 2010 at 1:55 pm (Oracle, PL/SQL, scripts)

PL-SQL/ORACLE:  Transformar Linhas para Coluna:

scott@DEV816> select job, deptno, count(*)
2    from emp
3   group by job, deptno
4  /

JOB           DEPTNO   COUNT(*)
——— ———- ———-
ANALYST           20          2
CLERK             10          1
CLERK             20          2
CLERK             30          1
MANAGER           10          1
MANAGER           20          1
MANAGER           30          1
PRESIDENT         10          1
SALESMAN          30          4

Transformando em colunas a mesma query:

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                       2
CLERK              1          2          1
MANAGER            1          1          1
PRESIDENT          1
SALESMAN                                 4

abraço a todos

Felipe Vidal de Oliveira Duarte

Gerente TI – Cristofoli // DBA ORACLE