개요
데이터 PIVOT을 수행하는 방법은 아래와 같이 세 가지 방법이 있습니다.
1.
SUM(DECODE()) 구문을 이용한 피벗
2.
프로시저를 이용한 동적 피벗
3.
PIVOT 구문을 이용한 피벗
이 문서에서는 프로시저(procedure)를 이용하여 동적으로 PIVOT을 수행하는 방법을 설명합니다.
동적 PIVOT 방식은 컬럼 값이 자주 변경되는 환경에서 유용합니다.
참고
테스트 환경은 Tibero 6 (DB 6.0 FS06_CS_1703) 버전 환경을 사용했습니다.
방법
테스트 테이블 생성
CREATE TABLE TEST(
YEARDATA VARCHAR(4),
DATA1 VARCHAR(6),
DATA2 VARCHAR(6)
);
SQL
복사
데이터 입력
INSERT INTO TEST VALUES ('1990','100000','999999');
INSERT INTO TEST VALUES ('1991','100001','999998');
INSERT INTO TEST VALUES ('1992','100002','999997');
INSERT INTO TEST VALUES ('1993','100003','999996');
INSERT INTO TEST VALUES ('1994','100004','999995');
INSERT INTO TEST VALUES ('1995','100005','999994');
INSERT INTO TEST VALUES ('1996','100006','999993');
INSERT INTO TEST VALUES ('1997','100007','999992');
INSERT INTO TEST VALUES ('1998','100008','999991');
INSERT INTO TEST VALUES ('1999','100009','999990');
INSERT INTO TEST VALUES ('2000','100010','999989');
INSERT INTO TEST VALUES ('2001','100011','999988');
COMMIT;
SELECT * FROM TEST;
YEARDATA DATA1 DATA2
-------- ------ ------
1990 100000 999999
1991 100001 999998
1992 100002 999997
1993 100003 999996
1994 100004 999995
1995 100005 999994
1996 100006 999993
1997 100007 999992
1998 100008 999991
1999 100009 999990
2000 100010 999989
2001 100011 999988
12 rows selected.
SQL
복사
프로시저를 이용한 동적 PIVOT 수행 방법
동적 PIVOT 방식은 컬럼 값이 자주 변경되는 환경에서 유용합니다.
(반면에 정적방식으로 수행시 테이블의 row값이 증가하는 경우에는 작성이 어렵습니다. )
CREATE OR REPLACE PROCEDURE SP_PIVOT(OUT_CURSOR OUT SYS_REFCURSOR)
IS
SQL_STMT VARCHAR2(30000);
V_ROWIDX NUMBER(3) := 0;
BEGIN
SQL_STMT := 'SELECT ';
FOR X IN
(
SELECT YEARDATA
FROM TEST
ORDER BY YEARDATA
)
LOOP
IF V_ROWIDX = 0 THEN
V_ROWIDX := V_ROWIDX + 1;
SQL_STMT := SQL_STMT||' MAX(DECODE(YEARDATA,'||X.YEARDATA||',NVL(DATA1,0),'''')) AS
"'||X.YEARDATA||'"';
ELSE
SQL_STMT := SQL_STMT||', MAX(DECODE(YEARDATA,'||X.YEARDATA||',NVL(DATA1,0),''''))
AS "'||X.YEARDATA||'"';
END IF;
END LOOP;
SQL_STMT := SQL_STMT||' FROM TEST';
SQL_STMT := SQL_STMT||' UNION ALL SELECT ';
V_ROWIDX := 0;
FOR Y IN
(
SELECT YEARDATA
FROM TEST
ORDER BY YEARDATA
)
LOOP
IF V_ROWIDX = 0 THEN
V_ROWIDX := V_ROWIDX + 1;
SQL_STMT := SQL_STMT||' MAX(DECODE(YEARDATA,'||Y.YEARDATA||',NVL(DATA2,0),'''')) AS "'||Y.YEARDATA||'"';
ELSE
SQL_STMT := SQL_STMT||', MAX(DECODE(YEARDATA,'||Y.YEARDATA||',NVL(DATA2,0),'''')) AS "'||Y.YEARDATA||'"';
END IF;
END LOOP;
SQL_STMT := SQL_STMT||' FROM TEST;';
OPEN OUT_CURSOR FOR(SQL_STMT);
DBMS_OUTPUT.PUT_LINE(SQL_STMT);
END;
/
Procedure 'SP_PIVOT' created.
SQL
복사
프로시저를 수행하여 쿼리를 동적으로 생성합니다.
(YEARDATA 증가시에도 동적으로 쿼리 구문 작성 가능합니다.)
SQL> SET SERVEROUTPUT ON
SQL> var out_cursor refcursor
SQL> exec SP_PIVOT(:out_cursor);
SELECT MAX(DECODE(YEARDATA,1990,NVL(DATA1,0),'')) AS "1990",
MAX(DECODE(YEARDATA,1991,NVL(DATA1,0),'')) AS "1991",
MAX(DECODE(YEARDATA,1992,NVL(DATA1,0),'')) AS "1992",
MAX(DECODE(YEARDATA,1993,NVL(DATA1,0),'')) AS "1993",
MAX(DECODE(YEARDATA,1994,NVL(DATA1,0),'')) AS "1994",
MAX(DECODE(YEARDATA,1995,NVL(DATA1,0),'')) AS "1995",
MAX(DECODE(YEARDATA,1996,NVL(DATA1,0),'')) AS "1996",
MAX(DECODE(YEARDATA,1997,NVL(DATA1,0),'')) AS "1997",
MAX(DECODE(YEARDATA,1998,NVL(DATA1,0),'')) AS "1998",
MAX(DECODE(YEARDATA,1999,NVL(DATA1,0),'')) AS "1999",
MAX(DECODE(YEARDATA,2000,NVL(DATA1,0),'')) AS "2000",
MAX(DECODE(YEARDATA,2001,NVL(DATA1,0),'')) AS "2001" FROM TEST UNION ALL
SELECT MAX(DECODE(YEARDATA,1990,NVL(DATA2,0),'')) AS "1990",
MAX(DECODE(YEARDATA,1991,NVL(DATA2,0),'')) AS "1991",
MAX(DECODE(YEARDATA,1992,NVL(DATA2,0),'')) AS "1992",
MAX(DECODE(YEARDATA,1993,NVL(DATA2,0),'')) AS "1993",
MAX(DECODE(YEARDATA,1994,NVL(DATA2,0),'')) AS "1994",
MAX(DECODE(YEARDATA,1995,NVL(DATA2,0),'')) AS "1995",
MAX(DECODE(YEARDATA,1996,NVL(DATA2,0),'')) AS "1996",
MAX(DECODE(YEARDATA,1997,NVL(DATA2,0),'')) AS "1997",
MAX(DECODE(YEARDATA,1998,NVL(DATA2,0),'')) AS "1998",
MAX(DECODE(YEARDATA,1999,NVL(DATA2,0),'')) AS "1999",
MAX(DECODE(YEARDATA,2000,NVL(DATA2,0),'')) AS "2000",
MAX(DECODE(YEARDATA,2001,NVL(DATA2,0),'')) AS "2001" FROM TEST;
SQL
복사
쿼리 수행 결과는 다음과 같습니다.
SQL> SELECT MAX(DECODE(YEARDATA,1990,NVL(DATA1,0),'')) AS "1990",
MAX(DECODE(YEARDATA,1991,NVL(DATA1,0),'')) AS "1991",
MAX(DECODE(YEARDATA,1992,NVL(DATA1,0),'')) AS "1992",
MAX(DECODE(YEARDATA,1993,NVL(DATA1,0),'')) AS "1993",
MAX(DECODE(YEARDATA,1994,NVL(DATA1,0),'')) AS "1994",
MAX(DECODE(YEARDATA,1995,NVL(DATA1,0),'')) AS "1995",
MAX(DECODE(YEARDATA,1996,NVL(DATA1,0),'')) AS "1996",
MAX(DECODE(YEARDATA,1997,NVL(DATA1,0),'')) AS "1997",
MAX(DECODE(YEARDATA,1998,NVL(DATA1,0),'')) AS "1998",
MAX(DECODE(YEARDATA,1999,NVL(DATA1,0),'')) AS "1999",
MAX(DECODE(YEARDATA,2000,NVL(DATA1,0),'')) AS "2000",
MAX(DECODE(YEARDATA,2001,NVL(DATA1,0),'')) AS "2001" FROM TEST UNION ALL
SELECT MAX(DECODE(YEARDATA,1990,NVL(DATA2,0),'')) AS "1990",
MAX(DECODE(YEARDATA,1991,NVL(DATA2,0),'')) AS "1991",
MAX(DECODE(YEARDATA,1992,NVL(DATA2,0),'')) AS "1992",
MAX(DECODE(YEARDATA,1993,NVL(DATA2,0),'')) AS "1993",
MAX(DECODE(YEARDATA,1994,NVL(DATA2,0),'')) AS "1994",
MAX(DECODE(YEARDATA,1995,NVL(DATA2,0),'')) AS "1995",
MAX(DECODE(YEARDATA,1996,NVL(DATA2,0),'')) AS "1996",
MAX(DECODE(YEARDATA,1997,NVL(DATA2,0),'')) AS "1997",
MAX(DECODE(YEARDATA,1998,NVL(DATA2,0),'')) AS "1998",
MAX(DECODE(YEARDATA,1999,NVL(DATA2,0),'')) AS "1999",
MAX(DECODE(YEARDATA,2000,NVL(DATA2,0),'')) AS "2000",
MAX(DECODE(YEARDATA,2001,NVL(DATA2,0),'')) AS "2001" FROM TEST;
1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001
------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
100000 100001 100002 100003 100004 100005 100006 100007 100008 100009 100010 100011
999999 999998 999997 999996 999995 999994 999993 999992 999991 999990 999989 999988
2 rows selected.
SQL
복사