-- 1.
---------------------------------------------------------------------------------
SELECT  D.DEPTNO
,       D.DNAME
,       E.JOB
,       E.SAL
FROM SCOTT.EMP E
JOIN SCOTT.DEPT D ON D.DEPTNO = E.DEPTNO

    DEPTNO DNAME          JOB              SAL
---------- -------------- --------- ----------
        10 ACCOUNTING     PRESIDENT       5000 
        10 ACCOUNTING     MANAGER         2450 
        10 ACCOUNTING     DEVELOPER       1100 
        10 ACCOUNTING     CLERK           1300 
        20 RESEARCH       ANALYST         3000 
        20 RESEARCH       MANAGER         2975 
        20 RESEARCH       CLERK            800 
        30 SALES          CLERK            950 
        30 SALES          SALESMAN        1500 
        30 SALES          SALESMAN        1250 
        30 SALES          SALESMAN        1250 
        30 SALES          SALESMAN        1600 
        30 SALES          MANAGER         2850 

 13개의 행이 선택됨 




-- 2.
---------------------------------------------------------------------------------
WITH TEMP
AS (SELECT  D.DEPTNO
    ,       D.DNAME
    ,       E.JOB
    ,       E.SAL
    FROM SCOTT.EMP E
    JOIN SCOTT.DEPT D ON D.DEPTNO = E.DEPTNO)
SELECT * 
FROM TEMP
PIVOT
(
    SUM(SAL) FOR DNAME IN ('ACCOUNTING', 'OPERATIONS', 'RESEARCH', 'SALES')
);


    DEPTNO JOB       'ACCOUNTING' 'OPERATIONS' 'RESEARCH'    'SALES'
---------- --------- ------------ ------------ ---------- ----------
        20 MANAGER                                   2975            
        20 CLERK                                      800            
        30 CLERK                                                 950 
        30 SALESMAN                                             5600 
        10 PRESIDENT         5000                                    
        10 CLERK             1300                                    
        30 MANAGER                                              2850 
        10 MANAGER           2450                                    
        20 ANALYST                                   3000            
        10 DEVELOPER         1100                                    

 10개의 행이 선택됨 





-- 3.
---------------------------------------------------------------------------------
WITH TEMP
AS (SELECT  D.DEPTNO
    ,       D.DNAME
    ,       E.JOB
    ,       E.SAL
    FROM SCOTT.EMP E
    JOIN SCOTT.DEPT D ON D.DEPTNO = E.DEPTNO)

SELECT * 
FROM   (SELECT  JOB
        ,       DNAME
        ,       SAL
        FROM TEMP)
PIVOT
(
    SUM(SAL) FOR DNAME IN ('ACCOUNTING', 'OPERATIONS', 'RESEARCH', 'SALES')
);


JOB       'ACCOUNTING' 'OPERATIONS' 'RESEARCH'    'SALES'
--------- ------------ ------------ ---------- ----------
CLERK             1300                     800        950 
SALESMAN                                             5600 
PRESIDENT         5000                                    
MANAGER           2450                    2975       2850 
DEVELOPER         1100                                    
ANALYST                                   3000            

 6개의 행이 선택됨