XSOFT Oracle DBMS Blog :: Pivot SQL - 짝수의 로우를 오른쪽으로 옮기는 방법

Oracle DBMS/AA2009/03/16 09:37

Pivot SQL - 짝수의 로우를 오른쪽으로 옮기는 방법

일반 프로그램이나 웹 브라우저에서 동일한 테이블의 데이터를 옆으로 반복하여 표시하고자 할 때 어떻게 이를 해결해야 할까? 이번시간에는 SQL을 통해 추출되는 형태를 자유자재로 변경하는 Pivot SQL에 대해 알아보고자 한다.

Pivot이란 무엇을 의미하는가? 사전상의 의미로는 '회전축, 회전하다' 등이 있는데 엑셀의 Pivot Chart를 연상하면 쉽다. 아래를 살펴보자.

<height to width>

COL1        COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10
----        ---- ---- ---- ---- ---- ---- ---- ---- ---- -----
01     ->   01   02   03   04   05   06   07   08   09   10
02
03
04
05
06
07
08
09
10

<width to height>

COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10          COL1
---- ---- ---- ---- ---- ---- ---- ---- ---- -----          ----
01   02   03   04   05   06   07   08   09   10       ->    01
                                                            02
                                                            03
                                                            04
                                                            05
                                                            06
                                                            07
                                                            08
                                                            09
                                                            10

이처럼 '가로->세로' 또는 '세로->가로'로 출력하도록 하는 방식을 Pivot SQL이라고 하는데 이번시간에는 짝수의 로우를 오른쪽으로 옮기는 방식을 알아보고자 한다.

-- create EMP table
CREATE TABLE EMP (EMPNO    NUMBER(4) NOT NULL,
                  ENAME    VARCHAR2(10),
                  JOB      VARCHAR2(9),
                  MGR      NUMBER(4),
                  HIREDATE DATE,
                  SAL      NUMBER(7, 2),
                  COMM     NUMBER(7, 2),
                  DEPTNO   NUMBER(2));

-- insert into data in EMP table
INSERT INTO EMP VALUES
        (7369, 'SMITH', 'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN', 'SALESMAN', 7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN', 7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES', 'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN', 7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

INSERT INTO EMP VALUES
        (7698, 'BLAKE', 'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK', 'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT', 'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN', 7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS', 'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES', 'CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

COMMIT;

-- select EMP
SELECT EMPNO,
       ENAME,
       JOB
FROM   EMP
ORDER BY EMPNO
;

-- before data
     EMPNO ENAME                JOB
---------- -------------------- ------------------
      7369 SMITH                CLERK
      7499 ALLEN                SALESMAN
      7521 WARD                 SALESMAN
      7566 JONES                MANAGER
      7654 MARTIN               SALESMAN
      7698 BLAKE                MANAGER
      7782 CLARK                MANAGER
      7788 SCOTT                ANALYST
      7839 KING                 PRESIDENT
      7844 TURNER               SALESMAN
      7876 ADAMS                CLERK
      7900 JAMES                CLERK
      7902 FORD                 ANALYST
      7934 MILLER               CLERK

-- after data
EMPNO_1 ENAME_1 JOB_1      EMPNO_2 ENAME_2 JOB_2
------- -------- ---------- ------- -------- ---------
   7369 SMITH    CLERK         7499 ALLEN    SALESMAN
   7521 WARD     SALESMAN      7566 JONES    MANAGER
   7654 MARTIN   SALESMAN      7698 BLAKE    MANAGER
   7782 CLARK    MANAGER       7788 SCOTT    ANALYST
   7839 KING     PRESIDENT     7844 TURNER   SALESMAN
   7876 ADAMS    CLERK         7900 JAMES    CLERK
   7902 FORD     ANALYST       7934 MILLER   CLERK

우선 홀수의 로우와 짝수의 로우를 구분할 수 있는 구분자 컬럼이 필요하다. 그럼 그 컬럼은 무엇으로 해야할까? 그렇다. 바로 지난시간에 살펴본 ROWNUM으로 구분을 할 수 있다.

SELECT ROWNUM,
       MOD(ROWNUM, 2) GUBN_1,
       EMPNO,
       ENAME,
       JOB
FROM   EMP
ORDER BY EMPNO
;

ROWNUM GUBN_1 EMPNO ENAME JOB
------ ------ ----- ------ ---------
     1      1 7369 SMITH CLERK
     2      0 7499 ALLEN SALESMAN
     3      1 7521 WARD   SALESMAN
     4      0 7566 JONES MANAGER
     5      1 7654 MARTIN SALESMAN
     6      0 7698 BLAKE MANAGER
     7      1 7782 CLARK MANAGER
     8      0 7788 SCOTT ANALYST
     9      1 7839 KING   PRESIDENT
    10      0 7844 TURNER SALESMAN
    11      1 7876 ADAMS CLERK
    12      0 7900 JAMES CLERK
    13      1 7902 FORD   ANALYST
    14      0 7934 MILLER CLERK
;

EMPNO로 정렬한 데이터에 ROWNUM을 사용하고 이 ROWNUM을 MOD() 함수로 가공하여 홀수와 짝수를 구분하였다.

이제 이 GUBN_1 컬럼을 이용하여 홀수와 짝수를 컬럼으로 나누어보겠다.

SELECT GUBN_1,
       DECODE(GUBN_1, 1, EMPNO) EMPNO_1,
       DECODE(GUBN_1, 1, ENAME) ENAME_1,
       DECODE(GUBN_1, 1, JOB)   JOB_1,
       DECODE(GUBN_1, 0, EMPNO) EMPNO_2,
       DECODE(GUBN_1, 0, ENAME) ENAME_2,
       DECODE(GUBN_1, 0, JOB)   JOB_2
FROM   (SELECT ROWNUM,
               MOD(ROWNUM, 2) GUBN_1,
               EMPNO,
               ENAME,
               JOB
        FROM   EMP
        ORDER BY EMPNO)
;

GUBN_1 EMPNO_1 ENAME_1 JOB_1     EMPNO_2 ENAME_2 JOB_2
------ ------- ------- --------- ------- ------- --------
     1    7369 SMITH   CLERK
     0                              7499 ALLEN   SALESMAN
     1    7521 WARD    SALESMAN
     0                              7566 JONES   MANAGER
     1    7654 MARTIN SALESMAN
     0                              7698 BLAKE   MANAGER
     1    7782 CLARK   MANAGER
     0                              7788 SCOTT   ANALYST
     1    7839 KING    PRESIDENT
     0                              7844 TURNER SALESMAN
     1    7876 ADAMS   CLERK
     0                              7900 JAMES   CLERK
     1    7902 FORD    ANALYST
     0                              7934 MILLER CLERK
;

DECODE() 함수를 이용하여 컬럼을 나누었다. 하지만 우리가 원하는 형태로는 나오지 않았다.
관건은 GUBN_1의 값이 0으로 되어 있는 로우를 위로 한칸 올려야 하는데 이를 어떻게 해야할까?

바로 GROUP 함수의 속성을 이용하면 된다.

GROUP 함수의 특징 중 하나가 있는데 바로 NULL값을 포함시키지 않는다는 것이다.
아래의 테스트에서 이를 살펴보자.

SELECT ROWNUM,
       TRUNC((LEVEL + 1) / 2) GUBN_1,
       DECODE(MOD(ROWNUM, 2), 1, TRUNC((LEVEL + 1) / 2) + 10, 0, NULL) GUBN_2
FROM   DUAL
CONNECT BY LEVEL <= 10
;

    ROWNUM     GUBN_1     GUBN_2
---------- ---------- ----------
         1          1         11
         2          1
         3          2         12
         4          2
         5          3         13
         6          3
         7          4         14
         8          4
         9          5         15
        10          5
;

GUBN_1 컬럼에서 2개의 로우씩 로우를 같은 값으로 주었고 GUBN_2에서는 GUBN_1 컬럼의 같은 값 그룹에서 2번째 로우는 NULL을 주었다.(ROWNUM은 컬럼 번호를 확인하게 위해 임의로 넣은 것임)

이제 이 상태에서 GUBN_1 값의 그룹을 묶어 GUBN_2의 MIN() 또는 MAX() 값을 뽑아보자.

SELECT TRUNC((LEVEL + 1) / 2) GUBN_1,
       MIN(DECODE(MOD(ROWNUM, 2), 1, TRUNC((LEVEL + 1) / 2) + 10, 0, NULL)) GUBN_2
FROM   DUAL
CONNECT BY LEVEL <= 10
GROUP BY TRUNC((LEVEL + 1) / 2)
ORDER BY GUBN_1
;

    GUBN_1     GUBN_2
---------- ----------
         1         11
         2         12
         3         13
         4         14
         5         15
;

보는 것처럼 GROUP 함수를 사용하니 NULL값이 제외되었다. 이처럼 특정 값의 그룹 내에서 GROUP 함수를 사용하였을
경우 그 값에서 NULL값을 제외할 수 있으므로 위에 고민하였던 문제에 적용하면 해결할 수 있다.

SELECT GUBN_2,
       GUBN_1,
       DECODE(GUBN_1, 1, EMPNO) EMPNO_1,
       DECODE(GUBN_1, 1, ENAME) ENAME_1,
       DECODE(GUBN_1, 1, JOB)   JOB_1,
       DECODE(GUBN_1, 0, EMPNO) EMPNO_2,
       DECODE(GUBN_1, 0, ENAME) ENAME_2,
       DECODE(GUBN_1, 0, JOB)   JOB_2
FROM   (SELECT ROWNUM,
               MOD(ROWNUM, 2) GUBN_1,
               TRUNC((ROWNUM + 1) / 2) GUBN_2,
               EMPNO,
               ENAME,
               JOB
        FROM   EMP
        ORDER BY EMPNO)
;

GUBN_2 GUBN_1 EMPNO_1 ENAME_1 JOB_1      EMPNO_2 ENAME_2 JOB_2
------ ------ ------- ------- ---------- ------- ------- --------
     1      1    7369 SMITH   CLERK
     1      0                               7499 ALLEN   SALESMAN
     2      1    7521 WARD    SALESMAN
     2      0                               7566 JONES   MANAGER
     3      1    7654 MARTIN SALESMAN
     3      0                               7698 BLAKE   MANAGER
     4      1    7782 CLARK   MANAGER
     4      0                               7788 SCOTT   ANALYST
     5      1    7839 KING    PRESIDENT
     5      0                               7844 TURNER SALESMAN
     6      1    7876 ADAMS   CLERK
     6      0                               7900 JAMES   CLERK
     7      1    7902 FORD    ANALYST
     7      0                               7934 MILLER CLERK
;

기존의 SQL에 GUBN_2 구분자를 추가로 만들어 홀수와 짝수를 하나의 GROUP으로 만들 수 있도록 하였다. 이제 이 GROUP을 묶어서 각 컬럼들에 GROUP 함수를 부여하면 원하는 형태로 만들 수 있다.

SELECT MIN(DECODE(GUBN_1, 1, EMPNO)) EMPNO_1,
       MIN(DECODE(GUBN_1, 1, ENAME)) ENAME_1,
       MIN(DECODE(GUBN_1, 1, JOB))   JOB_1,
       MIN(DECODE(GUBN_1, 0, EMPNO)) EMPNO_2,
       MIN(DECODE(GUBN_1, 0, ENAME)) ENAME_2,
       MIN(DECODE(GUBN_1, 0, JOB))   JOB_2
FROM   (SELECT EMPNO,
               ENAME,
               JOB,
               MOD(ROWNUM, 2) GUBN_1,
               TRUNC((ROWNUM + 1) / 2) GUBN_2
        FROM   EMP
        ORDER BY EMPNO)
GROUP BY GUBN_2
ORDER BY 1
;

EMPNO_1 ENAME_1 JOB_1      EMPNO_2 ENAME_2 JOB_2
------- -------- ---------- ------- -------- ---------
   7369 SMITH    CLERK         7499 ALLEN    SALESMAN
   7521 WARD     SALESMAN      7566 JONES    MANAGER
   7654 MARTIN   SALESMAN      7698 BLAKE    MANAGER
   7782 CLARK    MANAGER       7788 SCOTT    ANALYST
   7839 KING     PRESIDENT     7844 TURNER   SALESMAN
   7876 ADAMS    CLERK         7900 JAMES    CLERK
   7902 FORD     ANALYST       7934 MILLER   CLERK
;

드디어 우리가 원하는 형태로 데이터가 추출되었다. 이 시간에는 Pivot를 구현하기 위해 어떻게 집합을 가공하고 또 그 집합을 구하기 위해 그룹함수를 어떻게 이용해야 하는지 살펴보았다.

다음시간에도 Pivot과 관련된 재미있는 사례를 소개하도록 하겠다.

크리에이티브 커먼즈 라이선스

이 저작물은 크리에이티브 커먼즈 코리아 저작자표시-비영리-동일조건변경허락 2.0 대한민국 라이선스에 따라 이용하실 수 있습니다.

'Oracle DBMS > AA' 카테고리의 다른 글

Pivot SQL - 짝수의 로우를 오른쪽으로 옮기는 방법 (2)	2009/03/16
rownum을 효과적으로 사용하기 (0)	2009/03/09

Posted by 엑셥

TAG pivot, 가로세로, 세로가로

Trackback 0 Comment 2

TRACKBACK http://xsoft.tistory.com/trackback/2

댓글을 달아 주세요

extremedb

TISTORY 에 둥지를 새로 트셨네요.
블로그 개설 축하드립니다.
2009/03/27 12:27 [ ADDR : EDIT/ DEL : REPLY ]
엑셥

감사합니다 동규님 ^^
빨리 네이버 자료를 옮기는 작업을 해야하는데 짬이 안나네요..
앞으로도 많은 교류 부탁드리겠습니다...
2009/03/27 18:31 [ ADDR : EDIT/ DEL : REPLY ]