우선 deptno_10,deptno_20,deptno_30 이 가로로 나와야 하므로 쿼리는

SELECT ...deptno_10,

            ...deptno_20,

            ...deptno_30

FROM EMP

와 같은 형식이어야 할 것을 생각해야 함.

그런데 나와야 하는 데이타 값들이 모두 갯수임. 그렇다면 그냥 COUNT 만 해도 될 것.

한 라인 지날 때 COUNT 함수가 작동하게만 하면 됨. COUNT(1) 이나 COUNT(777) 이나 전부 1임.

그냥 한 행을 COUNT 했다는 것.

COUNT 는 지정한 열의 행이 몇 개인지를 리턴하므로 COUNT (DECODE (deptno, 10, 1)) 하면

DEPTNO 라는 열의 값이 10 일 때 몇개의 행인지 리턴하게 됨.

일단 전체 데이타를 보자.

SELECT ROWNUM, T.*
FROM EMP T

을 해 보면 아래와 같이 나옴.

ROWNUM 이 1인 행부터 보면 여기서 DEPTNO 가 10 이나, 20 이나, 30 일 때만

COUNT  하면 됨.

 SELECT COUNT (DECODE (deptno, 10, 1)) deptno_10,
            COUNT (DECODE (deptno, 20, 1)) deptno_20,
            COUNT (DECODE (deptno, 30, 1)) deptno_30
  FROM emp

이 쿼리가 제일 간단한 것임.

* 또 다른 방법 ***********************************************************

DEPTNO_10  DEPTNO_20  DEPTNO_30
-------------------------------
3                 5                  6

와 같은 모양을 만들기 위해 먼저 DEPTNO_10 이 3개, DEPTNO_20 이 5개, DEPTNO_30 이 6개임을 아래 쿼리로 구한다.

SELECT DEPTNO,

            COUNT(EMPNO) A
            FROM EMP
            GROUP BY DEPTNO

위를 실행하면 RESULTSET 은 아래처럼 나온다.

DEPTNO 가 세로로 나오는데 이걸 가로로 나오도록 하려면 대략

SELECT ... AS DEPTNO_10,

            ... AS DEPTNO_20,

            ... AS DEPTNO_30

FROM (

SELECT DEPTNO,COUNT(EMPNO) A
FROM EMP
GROUP BY DEPTNO

)

의 형태가 되어야 함. 따라서 일단 아래와 같은 생각을 해 본다(대각선 모양을 만들기 위한 과정임)

- 위 RESULTSET 으로부터 가로로 변환을 시켜야 한다.

- 위 RESULTSET 처럼 ROW 는 3개로 놔두되 컬럼도 3개로 일단 아래처럼 만들어야 한다.

DEPTNO_10  DEPTNO_20  DEPTNO_30
-------------------------------
NULL             NULL           6

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

NULL             5                NULL

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

3                  NULL           NULL

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

- 각 라인마다 실제 데이타는 하나이고 나머지는 NULL 이므로 DECODE 를 이용해야 함을 생각한다.

- 쿼리는 아래처럼 만들면 됨.

SELECT DECODE(DEPTNO, 10, A, NULL) deptno_10,
            DECODE(DEPTNO, 20, A, NULL) deptno_20,
            DECODE(DEPTNO, 30, A, NULL) deptno_30
FROM
(
  SELECT DEPTNO,COUNT(EMPNO) A
     FROM EMP
     GROUP BY DEPTNO
)

이 쿼리를 실행하면 아래와 같이 나옴.

- 이제 3개 라인을 하나의 라인으로 줄이기 위해 MIN 이나 MAX 함수를 쓰면 된다.

- null 이 들어 있는 컬럼은 어떤 함수에도 연산을 하지 않으므로 min 또는 max 함수를 쓰는 것.

그룹함수중 자료형에 무관하게 쓸 수 있는 함수로는  MAX 와 MIN 함수가 있는데, 어느 것을 적용해 도 NULL 값을 제외하면 C1 부터 C4 까지 각 컬럼 별로 하나의 행만 통과되므로 결과는 동일하다. 

SELECT MAX(DECODE(DEPTNO, 10, A, NULL)) deptno_10,
           MAX(DECODE(DEPTNO, 20, A, NULL)) deptno_20,
           MAX(DECODE(DEPTNO, 30, A, NULL)) deptno_30
FROM
(
  SELECT DEPTNO,COUNT(EMPNO) A
     FROM EMP
     GROUP BY DEPTNO
)

결과는 아래와 같다.

우선 DEPTNO ,clerk ,salesman, manager 가 가로로 나와야 하므로 쿼리는

SELECT ...DEPTNO ,

            ... clerk ,

            ... salesman,

            ... manager

FROM EMP

와 같은 형식이어야 할 것을 생각해야 함.

그런데 나와야 하는 데이타 값들이 모두 갯수임. 그렇다면 그냥 COUNT 만 해도 될 것.

한 라인 지날 때 COUNT 함수가 작동하게만 하면 됨. COUNT(1) 이나 COUNT(777) 이나 전부 1임.

그냥 한 행을 COUNT 했다는 것.

COUNT 는 지정한 열의 행이 몇 개인지를 리턴하므로 COUNT (DECODE (deptno, 10, 1)) 하면

DEPTNO 라는 열의 값이 10 일 때 몇개의 행인지 리턴하게 됨.

일단 전체 데이타를 보자.

SELECT ROWNUM, T.*
FROM EMP T

을 해 보면 아래와 같이 나옴.

ROWNUM 이 1인 행부터 보면 여기서 DEPTNO 는 그냥 뽑아 올 수 있는 것이고

JOB 이 무엇이냐만 DECODE 로 나눠주면서 COUNT 하면 됨.

결국

SELECT   deptno
       , COUNT (DECODE (job, 'CLERK', 1)) clerk
       , COUNT (DECODE (job, 'SALESMAN', 1)) salesman
       , COUNT (DECODE (job, 'MANAGER', 1)) manager
    FROM emp
GROUP BY deptno
 

이 쿼리가 제일 간단한 방법.

* 또 다른 방법

    DEPTNO      CLERK   SALESMAN    MANAGER
---------- ---------- ---------- ----------
        10          1           0              1
        20          2           0              1
        30          1           4              1

원래는 JOB 이라는 필드 안에 세로로 쫙 들어 있던 것들을 위와 같이 3개의 필드로 나눠야 하므로 CEIL 과 MOD ,DECODE 함수를 사용해 볼 생각을 해 본다.

일단 아래의 쿼리로 CLERK   SALESMAN    MANAGER 를 새로운 컬럼으로 만들어 보자.

SELECT ... DEPTNO,

            ... CLERK,

            ... SALESMAN,

            ... MANAGER

FROM ( ... )

와 같은 형식이 될 것.

일단 전체 데이타를 보자. 

SELECT ROWNUM, T.*
FROM EMP T

을 해 보면 아래와 같이 나옴.

일단 첫 컬럼을 DEPTNO 로 해야 하니 DEPTNO 로 GROUP BY 한 후 나머지들을 COUNT 해 볼 생각을 해 보자.

쿼리 던져 보니 그냥 ORDER BY 하면 어찌해야 할지 보인다.

SELECT *
FROM EMP
ORDER BY DEPTNO

위 쿼리 던지면 RESULTSET 은 아래와 같다.

이제 필요한건, DEPTNO 과 JOB 필드 뿐이고 JOB 필드만 세로로 되어 있는걸(=하나의 필드에 있는걸) 여러개의 필드로 만들면 된다.

SELECT DISTINCT DEPTNO, JOB,
COUNT(JOB) OVER(PARTITION BY DEPTNO, JOB ORDER BY DEPTNO ) CNT
FROM EMP
ORDER BY DEPTNO

해보면 아래와 같다.

그런데 찾아야 하는 필드는 DEPTNO, CLERK , MAMAGER , SALESMAN 뿐이므로

ANALYST 와 PRESIDENT 는 뺀다. 아래 쿼리 실행시킨다.

SELECT DISTINCT DEPTNO, JOB,

COUNT(JOB) OVER(PARTITION BY DEPTNO, JOB ORDER BY DEPTNO ) CNT
FROM EMP
WHERE JOB <> 'PRESIDENT'
AND JOB <> 'ALALYST'
ORDER BY DEPTNO

결과는 아래와 같다.

여기서도 COUNT 를 쓰면 되겠으나 지금 그렇게 구하자는게 아니므로 아래와 같이 한다.

SELECT DEPTNO,
DECODE(JOB, 'CLERK', CNT) CLERK,
DECODE(JOB, 'MANAGER', CNT) MANAGER,
DECODE(JOB, 'SALESMAN', CNT) SALESMAN
FROM
(
   SELECT DISTINCT DEPTNO, JOB,
   COUNT(JOB) OVER(PARTITION BY DEPTNO, JOB ORDER BY DEPTNO ) CNT
   FROM EMP
   WHERE JOB <> 'PRESIDENT'
   AND JOB <> 'ANALYST'
   ORDER BY DEPTNO

)

이렇게 하면 아래와 같이 나온다.

다시 GROUP  BY 하고 MAX 로 합친다. 그룹함수중 자료형에 무관하게 쓸 수 있는 함수로는  MAX 와 MIN 함수가 있는데, 어느 것을 적용해도 NULL 값을 제외하면 C1 부터 C4 까지 각 컬럼 별로 하나의 행만 통과되므로 결과는 동일하다. 

SELECT DEPTNO,
MAX(DECODE(JOB, 'CLERK', CNT,0)) CLERK,
MAX(DECODE(JOB, 'MANAGER', CNT,0)) MANAGER,
MAX(DECODE(JOB, 'SALESMAN', CNT,0)) SALESMAN
FROM
(
   SELECT DISTINCT DEPTNO, JOB,
   COUNT(JOB) OVER(PARTITION BY DEPTNO, JOB ORDER BY DEPTNO ) CNT
   FROM EMP
   WHERE JOB <> 'PRESIDENT'
   AND JOB <> 'ANALYST'
   ORDER BY DEPTNO
)
GROUP BY DEPTNO

이렇게 하면 아래와 같이 잘 나온다.

** 그냥 헛 지랄 해 본 거임.  

1. 먼저 각 데이타에 번호를 붙인다.

SELECT ROWNUM NO,

GUBUN

FROM SAM_TAB02;

아래 그림과 같이 나온다.

2. CEIL 함수를 써서 같은 줄(컬)럼에 들어갈 데이타 들에 같은 번호를 부여한다. 

4개의 컬럼에 나눠 담을 것이므로 CEIL(ROWNUM/4) 를 사용한다. 이렇게 하면 4개의 데이타 마다 같은 번호를 부여받게 된다( CEIL() 함수는 소수점이 있을경우 올림한 정수를, 소수점 없을 땐 그냥 정수를 그대로 리턴한다)

SELECT CEIL(NO/4) RNO, NO,

            GUBUN

FROM 

( 

  SELECT ROWNUM NO,

              GUBUN

   FROM SAM_TAB02

)

위를 실행하면 아래와 같이 나온다.

3. 이제 4개씩 묶인 것들에 대해 하나 하나 순차적으로 다시 번호를 할당한다. 이렇게 해야 그게 몇 번째 컬럼으로 쓰일 것인지를 구분할 수 있다. MOD(ROWNUM ,4) 함수를 이용한다. 옆의 MOD 함수는 

ROWNUM 을 4로 나눈 나머지들을 리턴한다. 따라서 1,2,3,0 의 값만 나오게 된다.

SELECT  CEIL(NO/4) RNO, 

        MOD(NO,4) CNO,

        NO,

        GUBUN

FROM 

( 

  SELECT ROWNUM NO,

              GUBUN

   FROM SAM_TAB02

)

4. 이제 대각선 모양으로 만들기 위해 DECODE 를 넣어보자.

SELECT  CEIL(NO/4) RNO, 

        MOD(NO,4) CNO,

        NO,

        DECODE(MOD(NO,4), 1 , GUBUN) C1,

        DECODE(MOD(NO,4), 2 , GUBUN) C2,

        DECODE(MOD(NO,4), 3 , GUBUN) C3,

        DECODE(MOD(NO,4), 0 , GUBUN) C4 --DECODE(MOD(NO,4), 4 , GUBUN) 가 아님에 주의!!!

FROM 

( 

  SELECT ROWNUM NO,

              GUBUN

   FROM SAM_TAB02

)

위의 쿼리를 실행시켜보면 데이터가 아래와 같이 대각선 모양으로 나온다. 

5. 이제 MAX 함수를 쓰고  CEIL로 GROUP BY 하여 같은 RNO를 갖는 데이타들을 하나의 ROW 로 만들어 주면 된다. 

그룹함수중 자료형에 무관하게 쓸 수 있는 함수로는  MAX 와 MIN 함수가 있는데, 어느 것을 적용해도 NULL 값을 제외하면 C1 부터 C4 까지 각 컬럼 별로 하나의 행만 통과되므로 결과는 동일하다. 

SELECT  CEIL(NO/4) RNO, 

        --MOD(NO,4) CNO, -- 더이상 필요없는 컬럼

        --NO, -- 더이상 필요없는 컬럼

        MAX(DECODE(MOD(NO,4), 1 , GUBUN)) C1,

        MAX(DECODE(MOD(NO,4), 2 , GUBUN)) C2,

        MAX(DECODE(MOD(NO,4), 3 , GUBUN)) C3,

        MAX(DECODE(MOD(NO,4), 0 , GUBUN)) C4

FROM 

( 

  SELECT ROWNUM NO,

              GUBUN

   FROM SAM_TAB02

)

GROUP BY CEIL(NO/4)

위 쿼리를 실행시켜 보면 최초 내가 원했던 모양으로 나오게 된다. 아래처럼 말이다.

1. 먼저 각 데이타에 번호를 붙인다.

SELECT ROWNUM NO,

       EMP_ID,

       EMP_NAME

       FROM TEMP

아래 그림과 같이 나온다.

2. CEIL 함수를 써서 같은 줄(컬럼)들에 들어갈 데이타 들에 같은 번호를 부여한다. 

5개의 컬럼에 나눠 담을 것이므로 CEIL(ROWNUM/5) 를 사용한다. 이렇게 하면 5개의 데이타 마다 같은 번호를 부여받게 된다( CEIL() 함수는 소수점이 있을경우 올림한 정수를, 소수점 없을 땐 그냥 정수를 그대로 리턴한다)

SELECT CEIL(NO/5) RNO, 

       NO,

       EMP_ID,

       EMP_NAME

FROM 

( 

    SELECT ROWNUM NO,

           EMP_ID,

           EMP_NAME

           FROM TEMP

)

위를 실행하면 아래와 같이 나온다.

3. 이제 5개씩 묶인 것들에 대해 하나 하나 순차적으로 다시 번호를 할당한다. 이렇게 해야 그게 몇 번째 컬럼으로 쓰일 것인지를 구분할 수 있다. MOD(ROWNUM ,5) 함수를 이용한다. 옆의 MOD 함수는 

ROWNUM 을 5로 나눈 나머지들을 리턴한다. 따라서 1,2,3,4,0 의 값만 나오게 된다.

SELECT  CEIL(NO/5) RNO, 

        MOD(NO,5) CNO,

        NO,

       EMP_ID,

       EMP_NAME

FROM 

( 

    SELECT ROWNUM NO,

           EMP_ID,

           EMP_NAME

           FROM TEMP

)

4. 이제 대각선 모양으로 만들기 위해 DECODE 를 넣어보자.

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

SELECT  CEIL(NO/5) RNO, 

        MOD(NO,5) CNO,

        NO,

        DECODE(MOD(NO,5), 1 , EMP_ID) ID_1,

        DECODE(MOD(NO,5), 1 , EMP_NAME) NAME_1,

        DECODE(MOD(NO,5), 2 , EMP_ID) ID_2,

        DECODE(MOD(NO,5), 2 , EMP_NAME) NAME_2,

        DECODE(MOD(NO,5), 3 , EMP_ID) ID_3,

        DECODE(MOD(NO,5), 3 , EMP_NAME) NAME_3,

        DECODE(MOD(NO,5), 4 , EMP_ID) ID_4,

        DECODE(MOD(NO,5), 4 , EMP_NAME) NAME_4,

        DECODE(MOD(NO,5), 0 , EMP_ID) ID_5,

        DECODE(MOD(NO,5), 0 , EMP_NAME) NAME_5

FROM 

( 

    SELECT ROWNUM NO,

           EMP_ID,

           EMP_NAME

           FROM TEMP

)

위의 쿼리를 실행시켜보면 데이터가 아래와 같이 대각선 모양으로 나온다. 

5. 이제 MAX 함수를 쓰고  CEIL로 GROUP BY 하여 같은 RNO를 갖는 데이타들을 하나의 ROW 로 만들어 주면 된다. 

그룹함수중 자료형에 무관하게 쓸 수 있는 함수로는  MAX 와 MIN 함수가 있는데, 어느 것을 적용해도 NULL 값을 제외하면 ID_1 부터 NAME_5 까지 각 컬럼 별로 하나의 행만 통과되므로 결과는 동일하다. 

실제 보여지는 컬럼은  ID_1 부터 NAME_5 까지 10개지만 ID_1,NAME_1 컬럼처럼 두 개 컬럼 데이타를 한 번에 가져와서 보여주는 것이므로 CEIL(NO/10) 이 아니라 CEIL(NO/5) 로 해야 한다.

SELECT  CEIL(NO/5) RNO, 

        --MOD(NO,5) CNO,

        --NO,

        MAX(DECODE(MOD(NO,5), 1 , EMP_ID)) ID_1,

        MAX(DECODE(MOD(NO,5), 1 , EMP_NAME)) NAME_1,

        MAX(DECODE(MOD(NO,5), 2 , EMP_ID)) ID_2,

        MAX(DECODE(MOD(NO,5), 2 , EMP_NAME)) NAME_2,

        MAX(DECODE(MOD(NO,5), 3 , EMP_ID)) ID_3,

        MAX(DECODE(MOD(NO,5), 3 , EMP_NAME)) NAME_3,

        MAX(DECODE(MOD(NO,5), 4 , EMP_ID)) ID_4,

        MAX(DECODE(MOD(NO,5), 4 , EMP_NAME)) NAME_4,

        MAX(DECODE(MOD(NO,5), 0 , EMP_ID)) ID_5,

        MAX(DECODE(MOD(NO,5), 0 , EMP_NAME)) NAME_5

FROM 

( 

    SELECT ROWNUM NO,

           EMP_ID,

           EMP_NAME

           FROM TEMP

)

GROUP BY CEIL(NO/5) 

ORDER BY RNO

위 쿼리를 실행시켜 보면 최초 내가 원했던 모양으로 나오게 된다. 아래처럼 말이다.

먼저 아래와 같은 형태로 만들고 난 후, decode 를 이용해서 row 들을 column 들로 변환해야 함.

각 deptno 별로 일련 번호 부여해야 함. 그래야 나중에 decode 문 쓴다.

위와 같이 소계가 나오는 데이타를 만들려면 아래의 쿼리를 실행한다.

DEPTNO 가 각각 갯수가 다르므로 MOD(ROWNUM/4) 를 해야 할지 MOD(ROWNUM/5)를 해야 할지 알 수 없다. 

따라서 ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) 를 사용해야 한다.

 
SELECT DEPTNO,
       ENAME,
       --REF,
       SUM_SAL,
       RNUM
FROM
(
   SELECT DEPTNO,
          ENAME,
          SUM(SAL) SUM_SAL,
          ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) RNUM,
          GROUPING_ID(DEPTNO, ENAME) REF
          FROM EMP
          GROUP BY ROLLUP(DEPTNO, ENAME)
)
WHERE REF <> 3

이제 최종적으로 뽑아야 하는 컬럼이 D10 E10      S10   D20 E20     S20   D30 E30      S30 등이므로,

SELECT ...DEPTNO_10,

           ... ENAME_10,

           ... SALARY_10,

.....

FROM ()

의 형식으로 뽑아내기 위해 아래 쿼리를 실행한다.

SELECT
DECODE(DEPTNO,10 , DEPTNO) DEPTNO_10,
DECODE(DEPTNO,10 , ENAME) ENAME_10,
DECODE(DEPTNO,10 , SUM_SAL) SAL_10,
DECODE(DEPTNO,20 , DEPTNO) DEPTNO_20,
DECODE(DEPTNO,20 , ENAME) ENAME_20,
DECODE(DEPTNO,20 , SUM_SAL) SAL_20,
DECODE(DEPTNO,30 , DEPTNO) DEPTNO_30,
DECODE(DEPTNO,30 , ENAME) ENAME_30,
DECODE(DEPTNO,30 , SUM_SAL) SAL_30
FROM
(
 SELECT DEPTNO,
        ENAME,
        --REF,
        SUM_SAL,
        RNUM
 FROM
 (
   SELECT DEPTNO,
          ENAME,
          SUM(SAL) SUM_SAL,
          ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) RNUM,
          GROUPING_ID(DEPTNO, ENAME) REF
          FROM EMP
          GROUP BY ROLLUP(DEPTNO, ENAME)
 )
 WHERE REF <> 3
)

위를 실행하면 아래와 같은 모양이 나온다.

RNUM 으로 GROUP BY 해 주면 된다.

NULL 값은 함수 연산에 포함되지 않으므로 최종적으로 RNUM 이 같은 것 들중 MAX 함수 처리해주면 된다.

아래와 같다.

SELECT
MAX(DECODE(DEPTNO,10 , DEPTNO)) DEPTNO_10 ,
MAX(DECODE(DEPTNO,10 , ENAME)) ENAME_10,
MAX(DECODE(DEPTNO,10 , SUM_SAL)) SAL_10,
MAX(DECODE(DEPTNO,20 , DEPTNO)) DEPTNO_20,
MAX(DECODE(DEPTNO,20 , ENAME)) ENAME_20,
MAX(DECODE(DEPTNO,20 , SUM_SAL)) SAL_20,
MAX(DECODE(DEPTNO,30 , DEPTNO)) DEPTNO_30,
MAX(DECODE(DEPTNO,30 , ENAME)) ENAME_30,
MAX(DECODE(DEPTNO,30 , SUM_SAL)) SAL_30,
RNUM
FROM
(
  SELECT DEPTNO,
  ENAME,
  --REF,
  SUM_SAL,
  RNUM
  FROM
 (
    SELECT DEPTNO,
           ENAME,
           SUM(SAL) SUM_SAL,
           ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) RNUM,
           GROUPING_ID(DEPTNO, ENAME) REF
           FROM EMP
           GROUP BY ROLLUP(DEPTNO, ENAME)
 )
 WHERE REF <> 3 -- 소계말고 총계 나오는 부분.
)
GROUP BY RNUM
ORDER BY RNUM

위 쿼리를 실행시키면 최종적인 모습은 아래와 같이 된다.

위 5번의 결과 RESULTSET 을 다시 보면 아래와 같다.

그런데 5번과 같은 방식으로 DEPT10 과 DEPT20 은 윗 줄에 나오게 하고 DEPT30 만 아래처럼 DEPT10 과 DEPT20 의 아래쪽에 나오게 하려면 어떻게 해야 할까?     

쿼리는 동일하다. 다만 DEPT30 부분을 가져오는 DECODE 부분을 모두 주석처리 한 후 UNION ALL 로 상하로 묶어주면 될 것 같다. 

아래 쿼리를 실행해 보자.

SELECT 

MAX(DECODE(DEPTNO,10 , DEPTNO)) DEPTNO_10 ,

MAX(DECODE(DEPTNO,10 , ENAME)) ENAME_10,

MAX(DECODE(DEPTNO,10 , SUM_SAL)) SAL_10,

MAX(DECODE(DEPTNO,20 , DEPTNO)) DEPTNO_20,

MAX(DECODE(DEPTNO,20 , ENAME)) ENAME_20,

MAX(DECODE(DEPTNO,20 , SUM_SAL)) SAL_20,

--MAX(DECODE(DEPTNO,30 , DEPTNO)) DEPTNO_30,

--MAX(DECODE(DEPTNO,30 , ENAME)) ENAME_30,

--MAX(DECODE(DEPTNO,30 , SUM_SAL)) SAL_30,

RNUM

FROM 

( 

  SELECT DEPTNO,

  ENAME,

  --REF,

  SUM_SAL,

  RNUM

  FROM

 ( 

    SELECT DEPTNO, 

           ENAME,

           SUM(SAL) SUM_SAL,

           ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) RNUM,

           GROUPING_ID(DEPTNO, ENAME) REF

           FROM EMP

           GROUP BY ROLLUP(DEPTNO, ENAME)

 )

 WHERE REF <> 3 -- 소계말고 총계 나오는 부분.

)

GROUP BY RNUM

UNION ALL

SELECT 

--MAX(DECODE(DEPTNO,10 , DEPTNO)) DEPTNO_10 ,

--MAX(DECODE(DEPTNO,10 , ENAME)) ENAME_10,

--MAX(DECODE(DEPTNO,10 , SUM_SAL)) SAL_10,

--MAX(DECODE(DEPTNO,20 , DEPTNO)) DEPTNO_20,

--MAX(DECODE(DEPTNO,20 , ENAME)) ENAME_20,

--MAX(DECODE(DEPTNO,20 , SUM_SAL)) SAL_20,

MAX(DECODE(DEPTNO,30 , DEPTNO)) DEPTNO_30,

MAX(DECODE(DEPTNO,30 , ENAME)) ENAME_30,

MAX(DECODE(DEPTNO,30 , SUM_SAL)) SAL_30,

NULL ,

NULL,

NULL,

RNUM

FROM 

( 

  SELECT DEPTNO,

  ENAME,

  --REF,

  SUM_SAL,

  RNUM

  FROM

 ( 

    SELECT DEPTNO, 

           ENAME,

           SUM(SAL) SUM_SAL,

           ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) RNUM,

           GROUPING_ID(DEPTNO, ENAME) REF

           FROM EMP

           GROUP BY ROLLUP(DEPTNO, ENAME)

 )

 WHERE REF <> 3 -- 소계말고 총계 나오는 부분.

)

GROUP BY RNUM

ORDER BY RNUM

를 실행하면 아래와 같다.

원하던 바대로 나오지를 않는다. ORDER BY 쪽의 문제로 DEPT10 과 DEPT30 이 교차되어 나온다.

따라서 DEPT10 이 먼저 나오도록 아래처럼 LEVEL 이라는 필드를 하나 더 만들어 준다.

SELECT 1 LEV,

MAX(DECODE(DEPTNO,10 , DEPTNO)) DEPTNO_10 ,

MAX(DECODE(DEPTNO,10 , ENAME)) ENAME_10,

MAX(DECODE(DEPTNO,10 , SUM_SAL)) SAL_10,

MAX(DECODE(DEPTNO,20 , DEPTNO)) DEPTNO_20,

MAX(DECODE(DEPTNO,20 , ENAME)) ENAME_20,

MAX(DECODE(DEPTNO,20 , SUM_SAL)) SAL_20,

--MAX(DECODE(DEPTNO,30 , DEPTNO)) DEPTNO_30,

--MAX(DECODE(DEPTNO,30 , ENAME)) ENAME_30,

--MAX(DECODE(DEPTNO,30 , SUM_SAL)) SAL_30,

RNUM

FROM 

( 

  SELECT DEPTNO,

  ENAME,

  --REF,

  SUM_SAL,

  RNUM

  FROM

 ( 

    SELECT DEPTNO, 

           ENAME,

           SUM(SAL) SUM_SAL,

           ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) RNUM,

           GROUPING_ID(DEPTNO, ENAME) REF

           FROM EMP

           GROUP BY ROLLUP(DEPTNO, ENAME)

 )

 WHERE REF <> 3 -- 소계말고 총계 나오는 부분.

)

GROUP BY RNUM

UNION ALL

SELECT 2 LEV,

--MAX(DECODE(DEPTNO,10 , DEPTNO)) DEPTNO_10 ,

--MAX(DECODE(DEPTNO,10 , ENAME)) ENAME_10,

--MAX(DECODE(DEPTNO,10 , SUM_SAL)) SAL_10,

--MAX(DECODE(DEPTNO,20 , DEPTNO)) DEPTNO_20,

--MAX(DECODE(DEPTNO,20 , ENAME)) ENAME_20,

--MAX(DECODE(DEPTNO,20 , SUM_SAL)) SAL_20,

MAX(DECODE(DEPTNO,30 , DEPTNO)) DEPTNO_30,

MAX(DECODE(DEPTNO,30 , ENAME)) ENAME_30,

MAX(DECODE(DEPTNO,30 , SUM_SAL)) SAL_30,

NULL ,

NULL,

NULL,

RNUM

FROM 

( 

  SELECT DEPTNO,

  ENAME,

  --REF,

  SUM_SAL,

  RNUM

  FROM

 ( 

    SELECT DEPTNO, 

           ENAME,

           SUM(SAL) SUM_SAL,

           ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY DEPTNO) RNUM,

           GROUPING_ID(DEPTNO, ENAME) REF

           FROM EMP

           GROUP BY ROLLUP(DEPTNO, ENAME)

 )

 WHERE REF <> 3 -- 소계말고 총계 나오는 부분.

)

GROUP BY RNUM

ORDER BY LEV, RNUM

GROUP BY GROUPING SETS 로 구한 RESULTSET 을 가로로 정렬해 본다.

    SELECT 

        DEPTNO,

        ENAME,

        JOB,

        SUM(SAL) SS

        FROM EMP T

        GROUP BY GROUPING SETS 

        (

            DEPTNO,

            ENAME,

            JOB

        )

위의 쿼리를 실행하면 아래 그림과 같이 

ENAME 별로 급여 SUM 한 것,

JOB 별로 급여를 SUM 한 것,

DEPTNO 별로 급여를 SUM 한 것들이 각각 묶음으로 서로 다른 필드와 ROW 에 나오게 된다.

위의 쿼리를 수정하여 아래와 같이 같은 ROW 에 ENAME, DEPTNO, JOB , 각 합계가 모두 맨 윗 라인부터 나오도록 해 본다.

최종적으로 아래와 같은 모습이 되어야 한다.

먼저 각각의 묶음들(ENAME 별, JOB 별, DEPTNO 별, 각 합계) 을 모두 한 컬럼에 모은다. 먼저 계속 하던 형식대로 세로로 쫙 나오도록 UNION ALL 로 묶는다. 묶으면서 각 묶음 별로 고유한 그룹 번호를 부여하고 그 그룹 안에서도 넘버링 한다.

(원래 테이블이 자주 사용되니까 WITH 구문을 사용한다)

WITH TT AS

(

    SELECT 

        DEPTNO,

        ENAME,

        JOB,

        SUM(SAL) SS

        FROM EMP T

        GROUP BY GROUPING SETS 

        (

            DEPTNO,

            ENAME,

            JOB

        )

)

SELECT ENAME,

       SS,

       GRP,

       RNUM

FROM

(       

    SELECT TO_CHAR(ENAME) ENAME, 

           DECODE(ENAME,NULL, NULL ,SS) SS, 

           DECODE(ENAME,NULL, NULL ,1 ) GRP,

           DECODE(ENAME,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY ENAME)) RNUM

           FROM TT

    UNION ALL

    SELECT TO_CHAR(JOB), 

           DECODE(JOB,NULL, NULL ,SS),

           DECODE(JOB,NULL, NULL ,2) ,

           DECODE(JOB,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY JOB))

           FROM TT

    UNION ALL

    SELECT TO_CHAR(DEPTNO) , 

           DECODE(DEPTNO,NULL, NULL ,SS), 

           DECODE(DEPTNO,NULL, NULL ,3) ,

           DECODE(DEPTNO,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY DEPTNO))

           FROM TT

)

WHERE GRP IS NOT NULL

위의 쿼리를 실행하면 아래와 같은 모습이 나온다. 맨 윗 부분은 캡처에서 잘렸음.

WITH TT AS

(

    SELECT 

        DEPTNO,

        ENAME,

        JOB,

        SUM(SAL) SS

        FROM EMP T

        GROUP BY GROUPING SETS 

        (

            DEPTNO,

            ENAME,

            JOB

        )

)

SELECT 

       DECODE(GRP,1,ENAME) ENAME,

       DECODE(GRP,1,SS) SUM_ENAME,

       DECODE(GRP,2,ENAME) JOB,

       DECODE(GRP,2,SS) SUM_JOB,       

       DECODE(GRP,3,ENAME) DEPTNO,

       DECODE(GRP,3,SS) SUM_DEPTNO,              

       GRP,

       RNUM

FROM

(       

    SELECT TO_CHAR(ENAME) ENAME, 

           DECODE(ENAME,NULL, NULL ,SS) SS, 

           DECODE(ENAME,NULL, NULL ,1 ) GRP,

           DECODE(ENAME,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY ENAME)) RNUM

           FROM TT

    UNION ALL

    SELECT TO_CHAR(JOB), 

           DECODE(JOB,NULL, NULL ,SS),

           DECODE(JOB,NULL, NULL ,2) ,

           DECODE(JOB,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY JOB))

           FROM TT

    UNION ALL

    SELECT TO_CHAR(DEPTNO) , 

           DECODE(DEPTNO,NULL, NULL ,SS), 

           DECODE(DEPTNO,NULL, NULL ,3) ,

           DECODE(DEPTNO,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY DEPTNO))

           FROM TT

)

WHERE GRP IS NOT NULL

 

 

위의 쿼리를 실행하면 아래와 같이 된다. 맨 윗 부분은 캡처되지 않았음.

WITH TT AS

(

    SELECT 

        DEPTNO,

        ENAME,

        JOB,

        SUM(SAL) SS

        FROM EMP T

        GROUP BY GROUPING SETS 

        (

            DEPTNO,

            ENAME,

            JOB

        )

)

SELECT MIN(DECODE(GRP,1,ENAME)) ENAME,--MAX 함수여도 아무 관계없다.

       MIN(DECODE(GRP,1,SS)) SUM_ENAME,--MAX 함수여도 아무 관계없다.

       MIN(DECODE(GRP,2,ENAME)) JOB,

       MIN(DECODE(GRP,2,SS)) SUM_JOB,       

       MIN(DECODE(GRP,3,ENAME)) DEPTNO,

       MIN(DECODE(GRP,3,SS)) SUM_DEPTNO,             

       --GRP,

       RNUM

FROM

(       

    SELECT TO_CHAR(ENAME) ENAME, 

           DECODE(ENAME,NULL, NULL ,SS) SS, 

           DECODE(ENAME,NULL, NULL ,1 ) GRP,

           DECODE(ENAME,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY ENAME)) RNUM

           FROM TT

    UNION ALL

    SELECT TO_CHAR(JOB), 

           DECODE(JOB,NULL, NULL ,SS),

           DECODE(JOB,NULL, NULL ,2) ,

           DECODE(JOB,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY JOB))

           FROM TT

    UNION ALL

    SELECT TO_CHAR(DEPTNO) , 

           DECODE(DEPTNO,NULL, NULL ,SS), 

           DECODE(DEPTNO,NULL, NULL ,3) ,

           DECODE(DEPTNO,NULL, NULL ,ROW_NUMBER() OVER(ORDER BY DEPTNO))

           FROM TT

)

WHERE GRP IS NOT NULL  

GROUP BY RNUM

ORDER BY TO_NUMBER(RNUM) ASC

 

 

위의 쿼리를 실행해 보면, 처음 의도한 대로 잘 나오는걸 확인할 수 있다.