COLUMN을 ROW 형태로 변환방법 정리
Updated:
1. 개요
앞장에서는 ROW를 COLUMN으로 변환하는 방법을 설명하였습니다. 이번에는 역으로 COLUMN형태를 ROW형태로 변환하는것을 설명하겠습니다. 칼러형태로 수평적으로 연결된 데이타를 레코드 형태로 수직으로 나열되어 있는 데이타로 변환한다는 이야기입니다. 오라클의 UNPIVOT, 테이블 복제와 DECODE/CASE사용방법, UNION ALL, ROLLUP 등의 방법이 있습니다.
가. 예제 테이블
1) EMP 테이블
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
|---|---|---|---|---|---|---|---|
| 7369 | SMITH | CLERK | 7902 | 1980/12/17 00:00:00 | 800 | 20 | |
| 7499 | ALLEN | SALESMAN | 7698 | 1981/02/20 00:00:00 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981/02/22 00:00:00 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981/04/02 00:00:00 | 2975 | 20 | |
| 7654 | MARTIN | SALESMAN | 7698 | 1981/09/28 00:00:00 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981/05/01 00:00:00 | 2850 | 30 | |
| 7782 | CLARK | MANAGER | 7839 | 1981/06/09 00:00:00 | 2450 | 10 | |
| 7788 | SCOTT | ANALYST | 7566 | 1987/04/19 00:00:00 | 3000 | 20 | |
| 7839 | KING | PRESIDENT | 1981/11/17 00:00:00 | 5000 | 10 | ||
| 7844 | TURNER | SALESMAN | 7698 | 1981/09/08 00:00:00 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987/05/23 00:00:00 | 1100 | 20 | |
| 7900 | JAMES | CLERK | 7698 | 1981/12/03 00:00:00 | 950 | 30 | |
| 7902 | FORD | ANALYST | 7566 | 1981/12/03 00:00:00 | 3000 | 20 | |
| 7934 | MILLER | CLERK | 7782 | 1982/01/23 00:00:00 | 1300 | 10 |
2) DEPT 테이블
| DEPTNO | DNAME | LOC |
|---|---|---|
| 10 | ACCOUNTING} | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
2. 사용예제
가. UNPIVOT 사용
이 방법은 오라클 11g 이상 버젼에서 사용가능한 방법입니다. 가장 간단한 방법중 하나로 속도도 좋은 장점을 가지고 있습니다.
아래의 1) SQL에서는 unpivot_test 테스트 테이블을 생성하고 테스트 한 사례입니다. Customer_id별로 A, B, C, D 칼럼을 가지고 있는데 각 칼럼별로 점수가 나와 있는 형태입니다. 이것을 결과값처럼 수직적인 레코드형태로 변환하라는 것이 요구사항입니다.
UNPIVOT 함수를 사용하여 아래의 1번 SQL처럼 사용할수 있습니다. quantity, product_code 칼럼명을 만들고 그에 대응하는 값들을 IN절로 처리하였습니다. 이때 처리되는 대상의 종류와 갯수를 알고 있어야 가능한 방법입니다.
1) SQL
SELECT *
FROM unpivot_test
UNPIVOT (quantity FOR product_code IN (a AS 'A', b AS 'B', c AS 'C', d AS 'D'));
- 실행계획
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 672 | 5 (0)|
|* 1 | VIEW | | 16 | 672 | 5 (0)|
| 2 | UNPIVOT | | | | |
| 3 | TABLE ACCESS FULL| UNPIVOT_TEST | 4 | 68 | 3 (0)|
-------------------------------------------------------------------------
- unpivot_test 테이블
| ID | CUSTOMER_ID | A | B | C | D |
|---|---|---|---|---|---|
| 1 | 101 | 10 | 20 | 30 | |
| 2 | 102 | 40 | 50 | ||
| 3 | 103 | 60 | 70 | 80 | 90 |
| 4 | 104 | 100 |
- 결과값
| ID | CUSTOMER_ID | PRODUCT_CODE | QUNTITY |
|---|---|---|---|
| 1 | 101 | A | 10 |
| 1 | 101 | B | 20 |
| 1 | 101 | C | 30 |
| 2 | 102 | A | 40 |
| 2 | 102 | C | 50 |
| 3 | 103 | A | 60 |
| 3 | 103 | B | 70 |
| 3 | 103 | C | 80 |
| 3 | 103 | D | 90 |
| 4 | 104 | A | 100 |
나. UNION ALL, 테이블 복제 사용
이번에는 가장 간단한 방법인 UNION ALL을 사용하는 방법과 테이블 복제를 통해 변환하는 방법을 알아보겠습니다. 예제 테이블이 동일하여 2개의 방법을 동시에 설명하도록 하겠습니다.
UNION ALL을 사용한 방법은 1번과 같으며 동일 테이블에 4번 접근하여 처리하는 방법입니다. 그래서 SQL 속도는 가장 좋지않은 방법으로 판단됩니다.
2번 SQL의 경우 테이블 복제기법을 사용하여 테이블에 1번만 접근하고 오라클 메모리에서 카테시안 프로덕트를 발생시켜 복제 테이블을 3개 더 만들어 처리하는 방법입니다. 많이 사용하는 SQL형태인데 주의점은 복제하는 데이타의 량이 많은 경우 성능장애를 발생시킬수 있으므로 주의하셔야 합니다. 보통 몇천건이하로만 사용하는 것을 권장드리며 데이타 량이 많은 경우에는 UNPIVOT이나 ROLLUP 복제를 사용하기를 바랍니다.
2번 SQL의 경우 CONNECT BY LEVEL 함수를 많이 사용합니다. 인라인뷰의 LEVEL이 1, 2, 3, 4 라는 숫자를 반환하는데 바깥쪽 테이블 TEST11 과 WHERE절 연결조건이 없습니다. 이런경우 MERGE JOIN CARTESIAN 이 발생되며 테이블 복제가 발생되며 CNT 즉 FLAG를 이용하여 DECODE, CASE처리하면 우리가 원하는 값들을 추출할수 있습니다.
1) UNION ALL SQL
SELECT COLL,DEPT,'1학년', FRE
FROM TEST11
UNION ALL
SELECT COLL,DEPT,'2학년',SUP
FROM TEST11
UNION ALL
SELECT COLL,DEPT,'3학년',JUN
FROM TEST11
UNION ALL
SELECT COLL,DEPT,'4학년',SEN
FROM TEST11
- 실행계획
------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48 | 1200 | 12 (0)|
| 1 | UNION-ALL | | | | |
| 2 | TABLE ACCESS FULL| TEST11 | 12 | 300 | 3 (0)|
| 3 | TABLE ACCESS FULL| TEST11 | 12 | 300 | 3 (0)|
| 4 | TABLE ACCESS FULL| TEST11 | 12 | 300 | 3 (0)|
| 5 | TABLE ACCESS FULL| TEST11 | 12 | 300 | 3 (0)|
------------------------------------------------------------------
2) 테이블 복제 SQL
SELECT *
FROM TEST11, (SELECT LEVEL CNT
FROM DUAL
CONNECT BY LEVEL < 5);
3) 테이블 복제 완성 SQL
SELECT COLL, DEPT, DECODE(CNT,1,'1학년',2,'2학년',3,'3학년',4,'4학년') KEY3,
DECODE(CNT,1,FRE,2,SUP,3,JUN,4,SEN) JUMSU
FROM TEST11, (SELECT LEVEL CNT
FROM DUAL
CONNECT BY LEVEL < 5);
- 실행계획
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 600 | 5 (0)|
| 1 | MERGE JOIN CARTESIAN | | 12 | 600 | 5 (0)|
| 2 | VIEW | | 1 | 13 | 2 (0)|
|* 3 | CONNECT BY WITHOUT FILTERING| | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)|
| 5 | BUFFER SORT | | 12 | 444 | 5 (0)|
| 6 | TABLE ACCESS FULL | TEST11 | 12 | 444 | 3 (0)|
------------------------------------------------------------------------------
- 원 테이블 (test11)
| COL | DEPT | FRE | SUP | JUN | SEN |
|---|---|---|---|---|---|
| 공과대학 | 항공우주공학과 | 120 | 140 | 140 | 120 |
| 공과대학 | 기계공학과 | 240 | 260 | 240 | 240 |
| 공과대학 | 조선공학과 | 118 | 138 | 136 | 126 |
| 공과대학 | 산업공학과 | 123 | 141 | 140 | 120 |
| 공과대학 | 전자공학과 | 126 | 141 | 140 | 128 |
| 공과대학 | 재료공학과 | 128 | 132 | 142 | 126 |
| 공과대학 | 화학공학과 | 122 | 141 | 141 | 122 |
| 공과대학 | 전기공학과 | 120 | 140 | 140 | 120 |
| 공과대학 | 컴퓨터공학과 | 124 | 145 | 142 | 121 |
| 공과대학 | 섬유공학과 | 126 | 144 | 140 | 120 |
| 공과대학 | 자동화공학과 | 122 | 142 | 144 | 125 |
| 공과대학 | 고분자공학과 | 121 | 142 | 143 | 124 |
- 최종값
| COLL | DEPT | KEY3 | JUMSU |
|---|---|---|---|
| 공과대학 | 항공우주공학과 | 1학년 | 120 |
| 공과대학 | 기계공학과 | 1학년 | 240 |
| 공과대학 | 조선공학과 | 1학년 | 118 |
| 공과대학 | 산업공학과 | 1학년 | 123 |
| 공과대학 | 전자공학과 | 1학년 | 126 |
| 공과대학 | 재료공학과 | 1학년 | 128 |
| 공과대학 | 화학공학과 | 1학년 | 122 |
| 공과대학 | 전기공학과 | 1학년 | 120 |
| 공과대학 | 컴퓨터공학과 | 1학년 | 124 |
| 공과대학 | 섬유공학과 | 1학년 | 126 |
| 공과대학 | 자동화공학과 | 1학년 | 122 |
| 공과대학 | 고분자공학과 | 1학년 | 121 |
| 공과대학 | 항공우주공학과 | 2학년 | 140 |
| 공과대학 | 기계공학과 | 2학년 | 260 |
| …… | 생략 | ||
| 공과대학 | 전기공학과 | 4학년 | 120 |
| 공과대학 | 컴퓨터공학과 | 4학년 | 121 |
| 공과대학 | 섬유공학과 | 4학년 | 120 |
| 공과대학 | 자동화공학과 | 4학년 | 125 |
| 공과대학 | 고분자공학과 | 4학년 | 124 |
- 원테이블을 3번 더 복제한 경우(MERGE JOIN CARTESIAN )
| COL | DEPT | FRE | SUP | JUN | SEN | CNT |
|---|---|---|---|---|---|---|
| 공과대학 | 항공우주공학과 | 120 | 140 | 140 | 120 | 1 |
| 공과대학 | 기계공학과 | 240 | 260 | 240 | 240 | 1 |
| 공과대학 | 조선공학과 | 118 | 138 | 136 | 126 | 1 |
| 공과대학 | 산업공학과 | 123 | 141 | 140 | 120 | 1 |
| 공과대학 | 전자공학과 | 126 | 141 | 140 | 128 | 1 |
| 공과대학 | 재료공학과 | 128 | 132 | 142 | 126 | 1 |
| 공과대학 | 화학공학과 | 122 | 141 | 141 | 122 | 1 |
| 공과대학 | 전기공학과 | 120 | 140 | 140 | 120 | 1 |
| 공과대학 | 컴퓨터공학과 | 124 | 145 | 142 | 121 | 1 |
| 공과대학 | 섬유공학과 | 126 | 144 | 140 | 120 | 1 |
| 공과대학 | 자동화공학과 | 122 | 142 | 144 | 125 | 1 |
| 공과대학 | 고분자공학과 | 121 | 142 | 143 | 124 | 1 |
| …. | ||||||
| 공과대학 | 전기공학과 | 120 | 140 | 140 | 120 | 2 |
| 공과대학 | 컴퓨터공학과 | 124 | 145 | 142 | 121 | 2 |
| 공과대학 | 섬유공학과 | 126 | 144 | 140 | 120 | 2 |
| 공과대학 | 자동화공학과 | 122 | 142 | 144 | 125 | 2 |
| …. | ||||||
| 공과대학 | 전기공학과 | 120 | 140 | 140 | 120 | 4 |
| 공과대학 | 컴퓨터공학과 | 124 | 145 | 142 | 121 | 4 |
| 공과대학 | 섬유공학과 | 126 | 144 | 140 | 120 | 4 |
| 공과대학 | 자동화공학과 | 122 | 142 | 144 | 125 | 4 |
| 공과대학 | 고분자공학과 | 121 | 142 | 143 | 124 | 4 |
다. ROLLUP 사용
이 방법은 ROLLUP을 연구하다 우연히 발견하게 된 방법입니다. 아래의 1번 SQL에서
ROLLUP((COLL,DEPT,FRE,SUP,JUN,SEN),0,1,2) 항목을 보게되면 SQL을 만드는데 필요한 칼럼들을 Composite Colmun 형태로 ( ) 으로 묶고 그 뒤에다 원하는 복제 테이블 숫자만큼 0, 1, 2 숫자를 넣으면 됩니다. 숫자를 3개 넣었으므로 복제가 3번 더 일어납니다. 이 숫자들은 grouping(0), grouping(1), grouping(2)라는 의미로 rollup 안에서 마법과 같은 현상을 일으켜 데이블 복제를 발생시키는 것입니다. 주의점은 숫자를 1개씩 올려줘야 합니다.
역시 테이블에 1번만 접근하여 처리하였습니다.
1) ROLLUP 복제
SELECT COLL,DEPT,FRE,SUP,JUN,SEN, MIN(FRE),
GROUPING_ID(COLL,0,1,2) A
FROM TEST11
GROUP BY ROLLUP((COLL,DEPT,FRE,SUP,JUN,SEN),0,1,2)
ORDER BY GROUPING_ID(COLL,0,1,2)
2) ROLLUP 완성문장
SELECT COLL, DEPT, DECODE(A,0,'1학년',1,'2학년',3,'3학년 ',7,'4학년') KEY3,
DECODE(A,0,FRE,1,SUP,3,JUN,7,SEN) JUMSU
FROM (SELECT COLL,DEPT,FRE,SUP,JUN,SEN, MIN(FRE), GROUPING_ID(COLL,0,1,2) A
FROM TEST11
GROUP BY ROLLUP((COLL,DEPT,FRE,SUP,JUN,SEN),0,1,2)
ORDER BY GROUPING_ID(COLL,0,1,2) )
WHERE COLL IS NOT NULL;
- ROLLUP 실행계획
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 1068 | 5 (40)|
| 1 | VIEW | | 12 | 1068 | 5 (40)|
| 2 | SORT ORDER BY | | 12 | 444 | 5 (40)|
|* 3 | FILTER | | | | |
| 4 | SORT GROUP BY ROLLUP| | 12 | 444 | 5 (40)|
| 5 | TABLE ACCESS FULL | TEST11 | 12 | 444 | 3 (0)|
-----------------------------------------------------------------------
3. 결언
이번에는 COLUMN을 ROW형태로 변환하는 방법을 정리해보았습니다. 코로나 사태가 오늘보니 확진자가 많이 줄었네요. 빨리 정상적인 일상생활을 하고싶습니다. 건강 조심하시기 바랍니다.