[SQL] Oracle Pivot 함수를 사용한 동적 항목, 값 처리

금융 데이터를 가공할 때 결산년월을 선택하여 해당 년도의 계정 과목을 보여주고, 값이 얼마인지 출력하기도 합니다. 

이번에는 기본적인 메타 관리를 원칙으로 데이터 항목, 데이터 값을 동적으로 처리하는 쿼리입니다. 가변적 컬럼을 pivot 함수 for in절에 넣을 수 있으면 좋지만, PL/SQL을 활용하지 않는 한 어려움이 있어서 원하는 결과를 위에서 받고, 아래서는 최종 결과를 리턴하는 방식으로 쿼리를 작성 하였습니다.

[테이블 메타 관리 ERD]

[테이블 생성]

/* EstenPark_테이블컬럼 */
CREATE TABLE ESP_TB_COLUNM (
 TB_UNIQ_NO NUMBER(9) NOT NULL, /* 테이블고유번호 */
 COLUMN_ID VARCHAR2(32) NOT NULL, /* 컬럼ID */
 COLUMN_HANGL_NM VARCHAR2(100) NOT NULL, /* 컬럼한글명 */
 COLUMN_POS_NO NUMBER(9) NOT NULL, /* 컬럼포지션번호 */
 REG_USER VARCHAR2(30) DEFAULT 'EstenPark' NOT NULL, /* 등록사용자 */
 REG_DTTM DATE DEFAULT SYSDATE NOT NULL /* 등록일시 */
)
 STORAGE (
  BUFFER_POOL DEFAULT
 )
 TABLESPACE COM_DATA
 LOGGING
 NOCOMPRESS
 NOCACHE
 NOPARALLEL
 NOROWDEPENDENCIES
 DISABLE ROW MOVEMENT;

COMMENT ON TABLE ESP_TB_COLUNM IS 'EstenPark_테이블컬럼|컬럼의 항목(이름)을 관리한다.|';

COMMENT ON COLUMN ESP_TB_COLUNM.TB_UNIQ_NO IS '테이블고유번호|테이블고유번호||';

COMMENT ON COLUMN ESP_TB_COLUNM.COLUMN_ID IS '컬럼ID|컬럼ID||';

COMMENT ON COLUMN ESP_TB_COLUNM.COLUMN_POS_NO IS '컬럼포지션번호|컬럼포지션번호||';

COMMENT ON COLUMN ESP_TB_COLUNM.REG_USER IS '등록사용자|등록사용자||';

COMMENT ON COLUMN ESP_TB_COLUNM.REG_DTTM IS '등록일시|등록일시||';

CREATE UNIQUE INDEX ESP_TB_COLUNM_PK
 ON ESP_TB_COLUNM (
  TB_UNIQ_NO ASC,
  COLUMN_ID ASC
 )
 STORAGE (
  BUFFER_POOL DEFAULT
 )
 NOLOGGING
 TABLESPACE COM_INDEX
 NOCOMPRESS
 NOSORT
 NOPARALLEL;

ALTER TABLE ESP_TB_COLUNM
 ADD
  CONSTRAINT ESP_TB_COLUNM_PK
  PRIMARY KEY (
   TB_UNIQ_NO,
   COLUMN_ID
  );

/* EstenPark_테이블정보 */
CREATE TABLE ESP_TB_INFO (
 TB_UNIQ_NO NUMBER(9) NOT NULL, /* 테이블고유번호 */
 OWNER VARCHAR2(50) NOT NULL, /* 소유자 */
 TB_ID VARCHAR2(32) NOT NULL, /* 테이블ID */
 TB_HANGL_NM VARCHAR2(100) NOT NULL, /* 테이블한글명 */
 TB_BASIC_TBS VARCHAR2(100), /* 테이블기본테이블스페이스 */
 REG_USER VARCHAR2(30) DEFAULT 'EstenPark' NOT NULL, /* 등록사용자 */
 REG_DTTM DATE DEFAULT SYSDATE NOT NULL /* 등록일시 */
)
 STORAGE (
  BUFFER_POOL DEFAULT
 )
 TABLESPACE COM_DATA
 LOGGING
 NOCOMPRESS
 NOCACHE
 NOPARALLEL
 NOROWDEPENDENCIES
 DISABLE ROW MOVEMENT;

COMMENT ON TABLE ESP_TB_INFO IS 'EstenPark_테이블정보|테이블 정보를 관리한다.|';

COMMENT ON COLUMN ESP_TB_INFO.TB_UNIQ_NO IS '테이블고유번호|테이블고유번호||';

COMMENT ON COLUMN ESP_TB_INFO.OWNER IS '소유자|소유자||';

COMMENT ON COLUMN ESP_TB_INFO.TB_ID IS '테이블ID|테이블ID||';

COMMENT ON COLUMN ESP_TB_INFO.TB_HANGL_NM IS '테이블한글명|테이블한글명||';

COMMENT ON COLUMN ESP_TB_INFO.TB_BASIC_TBS IS '테이블기본테이블스페이스|테이블기본테이블스페이스||';

COMMENT ON COLUMN ESP_TB_INFO.REG_USER IS '등록사용자|등록사용자||';

COMMENT ON COLUMN ESP_TB_INFO.REG_DTTM IS '등록일시|등록일시||';

CREATE UNIQUE INDEX ESP_TB_INFO_PK
 ON ESP_TB_INFO (
  TB_UNIQ_NO ASC
 )
 STORAGE (
  BUFFER_POOL DEFAULT
 )
 NOLOGGING
 TABLESPACE COM_INDEX
 NOCOMPRESS
 NOSORT
 NOPARALLEL;

CREATE UNIQUE INDEX ESP_TB_INFO_IDX
 ON ESP_TB_INFO (
  TB_ID ASC,
  OWNER ASC
 )
 STORAGE (
  BUFFER_POOL DEFAULT
 )
 NOLOGGING
 TABLESPACE COM_INDEX
 NOCOMPRESS
 NOSORT
 NOPARALLEL;

ALTER TABLE ESP_TB_INFO
 ADD
  CONSTRAINT ESP_TB_INFO_PK
  PRIMARY KEY (
   TB_UNIQ_NO
  );

ALTER TABLE ESP_TB_INFO
 ADD
  CONSTRAINT ESP_TB_INFO_IDX01
  UNIQUE (
   TB_ID,
   OWNER
  );

/* EstenPark_테이블컬럼값 */
CREATE TABLE ESP_TB_COLUMN_VAL (
 TB_UNIQ_NO NUMBER(9) NOT NULL, /* 테이블고유번호 */
 COLUMN_ID VARCHAR2(32) NOT NULL, /* 컬럼ID */
 ROW_NO NUMBER(9) NOT NULL, /* 행번호 */
 COLUMN_VAL VARCHAR2(100) NOT NULL, /* 컬럼값 */
 REG_USER VARCHAR2(30) DEFAULT 'EstenPark' NOT NULL, /* 등록사용자 */
 REG_DTTM DATE DEFAULT SYSDATE NOT NULL /* 등록일시 */
)
 STORAGE (
  BUFFER_POOL DEFAULT
 )
 TABLESPACE COM_DATA
 LOGGING
 NOCOMPRESS
 NOCACHE
 NOPARALLEL
 NOROWDEPENDENCIES
 DISABLE ROW MOVEMENT;

COMMENT ON TABLE ESP_TB_COLUMN_VAL IS 'EstenPark_테이블컬럼값|테이블에 저장된 컬럼의 값|';

COMMENT ON COLUMN ESP_TB_COLUMN_VAL.TB_UNIQ_NO IS '테이블고유번호|테이블고유번호||';

COMMENT ON COLUMN ESP_TB_COLUMN_VAL.COLUMN_ID IS '컬럼ID|컬럼ID||';

COMMENT ON COLUMN ESP_TB_COLUMN_VAL.COLUMN_VAL IS '컬럼값|컬럼값||';

COMMENT ON COLUMN ESP_TB_COLUMN_VAL.REG_USER IS '등록사용자|등록사용자||';

COMMENT ON COLUMN ESP_TB_COLUMN_VAL.REG_DTTM IS '등록일시|등록일시||';

CREATE UNIQUE INDEX ESP_TB_COLUMN_VAL_PK
 ON ESP_TB_COLUMN_VAL (
  TB_UNIQ_NO ASC,
  COLUMN_ID ASC,
  ROW_NO ASC
 )
 STORAGE (
  BUFFER_POOL DEFAULT
 )
 NOLOGGING
 TABLESPACE COM_INDEX
 NOCOMPRESS
 NOSORT
 NOPARALLEL;

ALTER TABLE ESP_TB_COLUMN_VAL
 ADD
  CONSTRAINT ESP_TB_COLUMN_VAL_PK
  PRIMARY KEY (
   TB_UNIQ_NO,
   COLUMN_ID,
   ROW_NO
  );

ALTER TABLE ESP_TB_COLUNM
 ADD
  CONSTRAINT ESP_TB_COLUNM_FK01
  FOREIGN KEY (
   TB_UNIQ_NO
  )
  REFERENCES ESP_TB_INFO (
   TB_UNIQ_NO
  );

ALTER TABLE ESP_TB_COLUMN_VAL
 ADD
  CONSTRAINT ESP_TB_COLUMN_VAL_FK01
  FOREIGN KEY (
   TB_UNIQ_NO,
   COLUMN_ID
  )
  REFERENCES ESP_TB_COLUNM (
   TB_UNIQ_NO,
   COLUMN_ID
  );

[데이터 생성]

SET DEFINE OFF;
Insert into ESP_TB_INFO
   (TB_UNIQ_NO, OWNER, TB_ID, TB_HANGL_NM, TB_BASIC_TBS, 
    REG_USER, REG_DTTM)
 Values
   (1, 'ESTENPARK', 'ESP_USER_INFO', 'ESP_사용자정보', 'COM_DATA', 
    'EstenPark', TO_DATE('11/18/2015 13:54:11', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ESP_TB_INFO
   (TB_UNIQ_NO, OWNER, TB_ID, TB_HANGL_NM, TB_BASIC_TBS, 
    REG_USER, REG_DTTM)
 Values
   (2, 'ESTENPARK', 'ES_USER_GRP', 'ESP_사용자그룹', 'COM_DATA', 
    'EstenPark', TO_DATE('11/18/2015 13:55:04', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

Insert into ESP_TB_COLUNM
   (TB_UNIQ_NO, COLUMN_ID, COLUMN_HANGL_NM, COLUMN_POS_NO, REG_USER, 
    REG_DTTM)
 Values
   (1, 'USER_ID', '사용자ID', 1, 'EstenPark', 
    TO_DATE('11/18/2015 13:55:43', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ESP_TB_COLUNM
   (TB_UNIQ_NO, COLUMN_ID, COLUMN_HANGL_NM, COLUMN_POS_NO, REG_USER, 
    REG_DTTM)
 Values
   (1, 'USER_NM', '사용자명', 2, 'EstenPark', 
    TO_DATE('11/18/2015 13:55:58', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ESP_TB_COLUNM
   (TB_UNIQ_NO, COLUMN_ID, COLUMN_HANGL_NM, COLUMN_POS_NO, REG_USER, 
    REG_DTTM)
 Values
   (1, 'USER_GRP_NO', '사용자그룹번호', 3, 'EstenPark', 
    TO_DATE('11/18/2015 13:56:21', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ESP_TB_COLUNM
   (TB_UNIQ_NO, COLUMN_ID, COLUMN_HANGL_NM, COLUMN_POS_NO, REG_USER, 
    REG_DTTM)
 Values
   (2, 'USER_GRP_NO', '사용자그룹번호', 1, 'EstenPark', 
    TO_DATE('11/18/2015 13:56:42', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ESP_TB_COLUNM
   (TB_UNIQ_NO, COLUMN_ID, COLUMN_HANGL_NM, COLUMN_POS_NO, REG_USER, 
    REG_DTTM)
 Values
   (2, 'USER_GRP_NM', '사용자그룹명', 2, 'EstenPark', 
    TO_DATE('11/18/2015 13:56:57', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

Insert into ESP_TB_COLUMN_VAL
   (TB_UNIQ_NO, COLUMN_ID, ROW_NO, COLUMN_VAL, REG_USER, 
    REG_DTTM)
 Values
   (1, 'USER_ID', 1, 'ParkSS', 'EstenPark', 
    TO_DATE('11/18/2015 14:04:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ESP_TB_COLUMN_VAL
   (TB_UNIQ_NO, COLUMN_ID, ROW_NO, COLUMN_VAL, REG_USER, 
    REG_DTTM)
 Values
   (1, 'USER_ID', 2, 'Judi', 'EstenPark', 
    TO_DATE('11/18/2015 14:04:22', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ESP_TB_COLUMN_VAL
   (TB_UNIQ_NO, COLUMN_ID, ROW_NO, COLUMN_VAL, REG_USER, 
    REG_DTTM)
 Values
   (1, 'USER_ID', 3, 'KimSM', 'EstenPark', 
    TO_DATE('11/18/2015 14:04:51', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ESP_TB_COLUMN_VAL
   (TB_UNIQ_NO, COLUMN_ID, ROW_NO, COLUMN_VAL, REG_USER, 
    REG_DTTM)
 Values
   (1, 'USER_NM', 1, '박상신', 'EstenPark', 
    TO_DATE('11/18/2015 14:05:44', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ESP_TB_COLUMN_VAL
   (TB_UNIQ_NO, COLUMN_ID, ROW_NO, COLUMN_VAL, REG_USER, 
    REG_DTTM)
 Values
   (1, 'USER_NM', 2, '쥬디', 'EstenPark', 
    TO_DATE('11/18/2015 14:05:59', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ESP_TB_COLUMN_VAL
   (TB_UNIQ_NO, COLUMN_ID, ROW_NO, COLUMN_VAL, REG_USER, 
    REG_DTTM)
 Values
   (1, 'USER_NM', 3, '김상민', 'EstenPark', 
    TO_DATE('11/18/2015 14:06:13', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ESP_TB_COLUMN_VAL
   (TB_UNIQ_NO, COLUMN_ID, ROW_NO, COLUMN_VAL, REG_USER, 
    REG_DTTM)
 Values
   (1, 'USER_GRP_NO', 1, '1', 'EstenPark', 
    TO_DATE('11/18/2015 14:06:36', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ESP_TB_COLUMN_VAL
   (TB_UNIQ_NO, COLUMN_ID, ROW_NO, COLUMN_VAL, REG_USER, 
    REG_DTTM)
 Values
   (1, 'USER_GRP_NO', 2, '1', 'EstenPark', 
    TO_DATE('11/18/2015 14:06:52', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ESP_TB_COLUMN_VAL
   (TB_UNIQ_NO, COLUMN_ID, ROW_NO, COLUMN_VAL, REG_USER, 
    REG_DTTM)
 Values
   (1, 'USER_GRP_NO', 3, '2', 'EstenPark', 
    TO_DATE('11/18/2015 14:07:09', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

[쿼리]

/*
  1. 테이블 기준으로 컬럼의 ID와 항목을 가져온다.
*/
SELECT    A.TB_UNIQ_NO /* 테이블고유번호 */
         ,A.TB_ID      /* 테이블아이디   */
         ,LISTAGG(B.COLUMN_POS_NO, ',') WITHIN GROUP (ORDER BY B.COLUMN_POS_NO) AS COLUMN_POS_NO_PATH
         ,LISTAGG(B.COLUMN_ID, ',')     WITHIN GROUP (ORDER BY B.COLUMN_POS_NO) AS COLUMN_ID_PATH
FROM      ESP_TB_INFO A 
         ,ESP_TB_COLUNM B  
WHERE     A.TB_ID = 'ESP_USER_INFO' /* [조건] 테이블ID */
AND       A.OWNER = 'ESTENPARK'     /* [조건] 테이블 소유자 */
AND       B.TB_UNIQ_NO = A.TB_UNIQ_NO
GROUP BY  A.TB_UNIQ_NO, A.TB_ID
;


/*
  2. 컬럼의 항목과 값을 모두 한번에 출력한다.
*/
SELECT    XX.*
FROM      (SELECT    X.*
           FROM      (SELECT    A.COLUMN_ID, A.COLUMN_POS_NO, 'No.' AS ROW_NO
                      FROM      ESP_TB_COLUNM A
                      WHERE     A.TB_UNIQ_NO = 1) 
           PIVOT     (MAX(COLUMN_ID) 
                        FOR COLUMN_POS_NO IN (1, 2, 3) /* [조건] COLUMN_POS_NO_PATH 컬럼의 값 */
                     ) X
           UNION ALL
           SELECT    X.*
           FROM      (SELECT    A.COLUMN_ID, TO_CHAR(A.ROW_NO) ROW_NO, A.COLUMN_VAL
                      FROM      ESP_TB_COLUMN_VAL A
                      WHERE     A.TB_UNIQ_NO = 1) 
           PIVOT    (MAX(COLUMN_VAL) 
                       FOR COLUMN_ID IN ('USER_ID', 'USER_NM', 'USER_GRP_NO') /* [조건] COLUMN_ID_PATH 컬럼의 값 */
           ) X
) XX
ORDER BY  DECODE(XX.ROW_NO, 'No.', '0', XX.ROW_NO)    

기본적으로 원하는 소유자와 테이블ID를 조건절에 대입 시켜서 유일한 값(Unique Index)을 가질 수 있습니다. 그 상태로 아래 쿼리에 [조건]에 대입을 하게 된다면 항목 순서와 일치하게 결과를 볼 수 있습니다.

[쿼리 최종 결과]