ROW를 COLUMN으로 변환방법 정리
Updated:
1. 개요
ROW를 COLUMN으로 변환은 SQL을 만들때 많이 나오는 방법중 하나입니다. 레코드 형태로 수직으로 나열되어 있는 데이타들을 칼럼형태로 바꾼다는 이야기입니다. 오라클에서는 DECODE/CASE사용방법, 계층형쿼리, PIVOT, Listagg, Xmlagg, Wm_concat 등의 방법이 있습니다.
이중 DECODE/CASE, 계층형쿼리는 모든 DB버젼에서, 9i에서는 Xmlagg, 10g에서는 Wm_concat, 11g 이상에서는 PIVOT, Listagg 함수가 사용가능합니다.
가. 예제 테이블
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. 사용예제
가. DECODE/CASE 사용
이 방법은 모든 RDBMS에서 사용 가능한 방법입니다. 10G 이전 버젼에서는 이 방법을 많이 사용했는데 속도는 wm_concat, listagg, pivot보다는 느리다. 실제 어느곳에서 테스트해보니 1만건을 전환하는데 decode/case는 6분, wm_cocnate 6초 정도로 속도차이가 많이 납니다.
아래의 1) SQL에서는 테스트 테이블을 만들지 않고 UNION ALL을 사용하여 간단하게 만들어 보았습니다. 2)번에서는 DECODE/CASE절을 사용하여 조건에 맞게끔 NAME1, NAME2, NAME3를 만들고나서 최종적으로 그룹함수의 특징인 NULL을 자동적으로 없애주는 특징을 이용하여 처리하면 홍길동, 김길동, 이길동이 수평적으로 연결되어 나옵니다. 결과값을 참고하기 바란다. 이때 MIN을 사용했는데 MAX를 사용하여도 무방합니다. 단지 NULL값만 없애주는 용도로 사용한것입니다.
1) SQL
SELECT *
FROM (SELECT '홍길동' RETURN#
FROM DUAL
UNION ALL
SELECT '김길동' RETURN#
FROM DUAL
UNION ALL
SELECT '이길동' RETURN#
FROM DUAL) TAB;
2) SQL
SELECT RETURN# ,
DECODE(RETURN#, '홍길동', RETURN#) "NAME_1" ,
DECODE(RETURN#, '김길동', RETURN#) "NAME_2" ,
DECODE(RETURN#, '이길동', RETURN#) "NAME_3"
FROM (SELECT '홍길동' RETURN#
FROM DUAL
UNION ALL
SELECT '김길동' RETURN#
FROM DUAL
UNION ALL
SELECT '이길동' RETURN#
FROM DUAL) TAB;
3) 최종 SQL
SELECT MIN(NAME_1) || ', ' || MIN(NAME_2) || ', ' || MIN(NAME_3) AS NAME
FROM (SELECT RETURN#,
DECODE(RETURN#, '홍길동', RETURN#) "NAME_1",
DECODE(RETURN#, '김길동', RETURN#) "NAME_2"
,DECODE(RETURN#, '이길동', RETURN#) "NAME_3"
FROM (SELECT '홍길동' RETURN#
FROM DUAL
UNION ALL
SELECT '김길동' RETURN#
FROM DUAL
UNION ALL
SELECT '이길동' RETURN#
FROM DUAL) TAB) ;
- 2)번 결과값
| RETRUN# | NAME#1 | NAME#2 | NAME#3 |
|---|---|---|---|
| 홍길동 | 홍길동 | ||
| 김길동 | 김길동 | ||
| 이길동 | 이길동 |
- 최종 결과값
| NAME |
|---|
| 홍길동, 김길동, 이길동 |
나. DECODE/CASE 사용
이번에는 다른 예를 들어보겠습니다. 각 부서별 JOB별로 몇명인지를 구하도록 하라. 최종값은 아래와 같습니다.
먼저 1)번 SQL을 사용하여 EMP와 DEPT테이블에서 원하는 칼럼들을 가져온다. 1)번 결과값처럼 데이타가 추출되는데 이 테이타를 2)번 SQL처럼 DECODE/CASE절을 사용하여 각 JOB별로 필요 데이타를 추출한다. 그리고 최종적으로 SUM 함수로 데이타를 카운트하여 원하는 최종값을 추출한다. 많이 사용하는 SQL방식이니 기억하기 바랍니다.
| DNAME | CLERK | MANAGER | PRESIDENT | ANALYST | SALESMAN |
|---|---|---|---|---|---|
| ACCOUNTING | 1 | 1 | 1 | 0 | 0 |
| RESEARCH | 2 | 1 | 0 | 2 | 0 |
| SALES | 1 | 1 | 0 | 0 | 4 |
1) SQL
select d.dname, e.job,count(e.empno) member
from emp e, dept d
where e.deptno = d.deptno
group by d.dname,e.job
order by d.dname;
2) SQL
select dname,
decode(job,'CLERK',member,0) clerk,
decode(job,'MANAGER',member,0) manager,
decode(job,'PRESIDENT',member,0) president,
decode(job,'ANALYST',member,0) analyst,
decode(job,'SALESMAN',member,0) salesman
from (select e.job, d.dname,count(e.empno) member
from emp e, dept d
where e.deptno = d.deptno
group by d.dname,e.job
order by d.dname);
3) SQL
select dname,
sum(decode(job,'CLERK',member,0)) clerk,
sum(decode(job,'MANAGER',member,0)) manager,
sum(decode(job,'PRESIDENT',member,0)) president,
sum(decode(job,'ANALYST',member,0)) analyst,
sum(decode(job,'SALESMAN',member,0)) salesman
from ( select e.job, d.dname,count(e.empno) member
from emp e, dept d
where e.deptno = d.deptno
group by d.dname,e.job
order by d.dname)
group by dname;
1) SQL결과값
| DNAME | JOB | MEMBER |
|---|---|---|
| ACCOUNTING | CLERK | 1 |
| ACCOUNTING | MANAGER | 1 |
| ACCOUNTING | PRESIDENT | 1 |
| RESEARCH | ANALYST | 2 |
| RESEARCH | CLERK | 2 |
| RESEARCH | MANAGER | 1 |
| SALES | CLERK | 1 |
| SALES | MANAGER | 1 |
| SALES | SALESMAN | 4 |
2) SQL결과값
| DNAME | CLERK | MANAGER | PRESIDENT | ANALYST | SALESMAN |
|---|---|---|---|---|---|
| ACCOUNTING | 1 | 0 | 0 | 0 | 0 |
| ACCOUNTING | 0 | 1 | 0 | 0 | 0 |
| ACCOUNTING | 0 | 0 | 1 | 0 | 0 |
| RESEARCH | 0 | 0 | 0 | 2 | 0 |
| RESEARCH | 2 | 0 | 0 | 0 | 0 |
| RESEARCH | 0 | 1 | 0 | 0 | 0 |
| SALES | 1 | 0 | 0 | 0 | 0 |
| SALES | 0 | 1 | 0 | 0 | 0 |
| SALES | 0 | 0 | 0 | 0 | 4 |
다. PIVOT 사용
이번에는 11G 부터 지원이 되는 PIVOT함수를 사용하여 처리해보겠습니다. 이 함수는 놀랍게도 위의 나항의 1)번 SQL처럼 SUM(DECODE) 방식으로 내부적으로 처리된다. 아래의 2)번과 3)번 SQL 형태를 보면 쉽게 이해할수 있으리라 생각한다. PIVOT은 반대 개념인 UNPIVOT도 지원하고 있으나 LISTAGG는 UNLISTAGG 함수가 없습니다.
10G에서는 사실상 속도가 나오는 방법은 vm_concat 함수밖에 없는데 이 함수는 오라클에서 인증해주는 함수가 아니다란걸 알아야 한다. 속도 관련해서 bug가 있으며 10.2.0.4 window 버젼에서 10.2.0.5 버젼으로 패치하면 속도저하가 일어나므로 주의하여야 합니다.
1) SQL
select *
from (select d.dname, e.job, e.empno
from emp e, dept d
where e.deptno = d.deptno)
pivot ( count(empno) for job in ('CLERK' clerk ,'MANAGER' manager,
'PRESIDENT' president, 'ANALYST' analyst, 'SALESMAN' saleman)
);
2) PIVOT 사용
Select * from t1 pivot ( sum(c3) for c2 in (1,2,3,4,5) );
3) PIVOT 내부
Select c1,
sum(case when (c2 = 1) then c3 end) as "1",
sum(case when (c2 = 2) then c3 end) as "2",
sum(case when (c2 = 3) then c3 end) as "3",
sum(case when (c2 = 4) then c3 end) as "4",
sum(case when (c2 = 5) then c3 end) as "5"
from t1
group by c1;
- 결과값
| DNAME | CLERK | MANAGER | PRESIDENT | ANALYST | SALESMAN |
|---|---|---|---|---|---|
| ACCOUNTING | 1 | 1 | 1 | 0 | 0 |
| RESEARCH | 2 | 1 | 0 | 2 | 0 |
| SALES | 1 | 1 | 0 | 0 | 4 |
라. Listagg 사용
Listagg 함수는 11G 이상에서 ROW를 COLUMN으로 변환할때 사용할수 있는 가장 편리한 함수중 하나입니다. Grouping 하고자하는 칼럼을 Group By 로 묶은후 가로로 나열하고자 하는 칼럼을 Listagg에 명시후 Within Group 에서 나열 순서를 Order BY로 지정한다. 1)번, 2)번 SQL을 비교해보겠습니다.
LISTAGG[(,]) WITHIN GROUP(ORDER BY) [OVER (PARTITION BY)]
1) SQL
SELECT DEPTNO, LISTAGG(ENAME, ‘,‘) WITHIN GROUP(ORDER BY HIREDATE) AS AGG_ENAME
FROM EMP
GROUP BY DEPTNO;
2) SQL
SELECT DEPTNO, ENAME, HIREDATE,
LISTAGG(ENAME, ‘,‘) WITHIN GROUP(ORDER BY HIREDATE) OVER(PARTITION BY DEPTNO) AS AGG_ENAME
FROM EMP;
1) 결과값
| DEPTNO | AGG_ENAME |
|---|---|
| 10 | CLARK,KING,MILLER |
| 20 | SMITH,JONES,FORD,SCOTT,ADAMS |
| 30 | ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES |
2) 결과값
| DEPTNO | ENAME | HIREDATE | AGG_ENAME | |
|---|---|---|---|---|
| 10 | CLARK | 1981/06/09 | 00:00:00 | CLARK, KING, MILLER |
| 10 | KING | 1981/11/17 | 00:00:00 | CLARK, KING, MILLER |
| 10 | MILLER | 1982/01/23 | 00:00:00 | CLARK, KING, MILLER |
| 20 | SMITH | 1980/12/17 | 00:00:00 | SMITH, JONES, FORD, SCOTT, ADAMS |
| 20 | JONES | 1981/04/02 | 00:00:00 | SMITH, JONES, FORD, SCOTT, ADAMS |
| 20 | FORD | 1981/12/03 | 00:00:00 | SMITH, JONES, FORD, SCOTT, ADAMS |
| 20 | SCOTT | 1987/04/19 | 00:00:00 | SMITH, JONES, FORD, SCOTT, ADAMS |
| 20 | ADAMS | 1987/05/23 | 00:00:00 | SMITH, JONES, FORD, SCOTT, ADAMS |
| 30 | ALLEN | 1981/02/20 | 00:00:00 | ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES |
| 30 | WARD | 1981/02/22 | 00:00:00 | ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES |
| 30 | BLAKE | 1981/05/01 | 00:00:00 | ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES |
| 30 | TURNER | 1981/09/08 | 00:00:00 | ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES |
| 30 | MARTIN | 1981/09/28 | 00:00:00 | ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES |
| 30 | JAMES | 1981/12/03 | 00:00:00 | ALLEN, WARD, BLAKE, TURNER, MARTIN, JAMES |
3. 실전 사용예제
wm_concat, listagg, pivot 같은 함수로 인해 속도가 갑자기 저하되면 어떻게 하겠습니까? 실제 일어났던 상황이었는데. 위쪽에 기술한 10.2.0.4 버젼을 0.5로 패치하니 잘 수행되던 wm_concat가 속도가 갑자기 저하되어 1만건 처리하는데 6초정도 소요되는게 3분으로 급격히 화면속도가 떨어졌습니다. DBMS의 특징은 한번 패치하면 패치를 취소하는게 상당히 어려운법이죠. 물론 파라미터를 사용해서 그 패치만 무효화하는 방법도 있지만 DBMS을 더이상 변경 안할려고 하는 그 상황에서 응급으로 대응한 SQL이 2번 SQL입니다.
아래의 가번 TEST SQL은 EMP테이블을 이용하여 테스트가능 하도록 만든 SQL으로 나번 실전SQL에 사용한 개념과 동일한 SQL입니다.
원테이블에서 데이타를 점검해보니 칼럼으로 변경해야할 대상 레코드건수가 각각 최대 6건이 넘지 않는다는 업무 조사를 하고나서 숫자를 자동으로 부여하게끔 row_number 함수를 사용하였습니다. 이 ranking 펑션에서 나오는 숫자를 flag로 삼아 바깥단에서 lead 펑션을 사용하여 데이타를 추출하고 연결자를 사용하여 데이타를 연결하였습니다.
가. TEST SQL
1) listagg 사용
SELECT deptno, listagg(ename,', ') within group( order by hiredate)
FROM emp
GROUP BY deptno;
2) lead over() sql
select DEPTNO, ENAME
from (SELECT DEPTNO,
RTRIM(ENAME||','||
lead(ENAME, 1) OVER(partition by DEPTNO ORDER BY HIREDATE )||','||
lead(ENAME, 2) OVER(partition by DEPTNO ORDER BY HIREDATE )||','||
lead(ENAME, 3) OVER(partition by DEPTNO ORDER BY HIREDATE )||','||
lead(ENAME, 4) OVER(partition by DEPTNO ORDER BY HIREDATE )||','||
lead(ENAME, 5) OVER(partition by DEPTNO ORDER BY HIREDATE )||','||
lead(ENAME, 6) OVER(partition by DEPTNO ORDER BY HIREDATE ), ' ,')
as ename, flag
from (select deptno, ename, sal, hiredate,
row_number() over(partition by deptno order by hiredate) flag
FROM (SELECT deptno, ename, sal, hiredate
FROM emp
) A ) )
where flag = 1
order by 1, 2 ;
| DEPTNO | ENAME |
|---|---|
| 10 | KING, MILLER, CLARK |
| 20 | SCOTT, ADAMS, FORD, SMITH, JONES |
| 30 | JAMES, TURNER, BLAKE, ALLEN, WARD, MARTIN |
나. 실전 SQL
1) 원 SQL
SELECT PJT_SEQ, CKLT_REG_SEQ, wm_concat(CL_ID_NM), wm_concat(CL_ID_ENG_NM)
FROM QG_PJT_EXTR_ITEM
GROUP BY PJT_SEQ, CKLT_REG_SEQ ;
*12c에서는 wm_concat가 없어짐
2) 변형 SQL
select PJT_SEQ, CKLT_REG_SEQ , CL_ID_NM as CL_ID_NM, CL_ID_ENG_NM as CL_ID_ENG_NM
from (SELECT PJT_SEQ,
CKLT_REG_SEQ ,
RTRIM(CL_ID_NM||','||
lead(CL_ID_NM, 1) OVER(partition by PJT_SEQ, CKLT_REG_SEQ ORDER BY PJT_SEQ,CKLT_REG_SEQ, flag )||','||
lead(CL_ID_NM, 2) OVER(partition by PJT_SEQ, CKLT_REG_SEQ ORDER BY PJT_SEQ, CKLT_REG_SEQ, flag )||','||
lead(CL_ID_NM, 3) OVER(partition by PJT_SEQ, CKLT_REG_SEQ ORDER BY PJT_SEQ, CKLT_REG_SEQ, flag ),',') as CL_ID_NM,
RTRIM(CL_ID_ENG_NM||','||
lead(CL_ID_ENG_NM, 1) OVER(partition by PJT_SEQ, CKLT_REG_SEQ ORDER BY PJT_SEQ, CKLT_REG_SEQ, flag ) ||','||
lead(CL_ID_ENG_NM, 2) OVER(partition by PJT_SEQ, CKLT_REG_SEQ ORDER BY PJT_SEQ, CKLT_REG_SEQ, flag ) ||','||
lead(CL_ID_ENG_NM, 3) OVER(partition by PJT_SEQ, CKLT_REG_SEQ ORDER BY PJT_SEQ, CKLT_REG_SEQ, flag ),',') as cL_ID_ENG_NM , flag
from (select PJT_SEQ, CKLT_REG_SEQ, CL_ID_NM, CL_ID_ENG_NM,
row_number() over(partition by PJT_SEQ, CKLT_REG_SEQ order by PJT_SEQ, CKLT_REG_SEQ, CL_ID_NM) flag
FROM (SELECT PJT_SEQ, CKLT_REG_SEQ, CL_ID_NM, CL_ID_ENG_NM
FROM QG_PJT_EXTR_ITEM
order by PJT_SEQ, CKLT_REG_SEQ, CL_ID_NM, CL_ID_ENG_NM asc ) A ) )
where flag = 1
order by 1, 2 ;
1) 결과값
| PJT_SEQ | CKLT_REG_SEQ | CL_ID_NM | CL_ID_ENG_NM |
|---|---|---|---|
| PJ200109.0012053 | CR00304872 | 토목 | Civil |
| PJ200109.0012053 | CR00316367 | 공통가설,기타,전기(통신),토목 | Common facilities,etc,Electrical,Civil |
| PJ200109.0012053 | CR00317833 | 기타,전기(통신),토목 | etc,Electrical,Civil |
| PJ200109.0012053 | CR00318187 | 기타 | Etc |
2) 원테이블
| PJT_SEQ | CKLT_REG_SEQ | CL_ID_NM | CL_ID_ENG_NM | flag |
|---|---|---|---|---|
| PJ200109.0012053 | CR00304872 | 토목 | Civil | 1 |
| PJ200109.0012053 | CR00316367 | 공통가설 | Common facilities | 1 |
| PJ200109.0012053 | CR00316367 | 기타 | etc | 2 |
| PJ200109.0012053 | CR00316367 | 전기(통신) | Electrical | 3 |
| PJ200109.0012053 | CR00316367 | 토목 | Civil | 4 |
| PJ200109.0012053 | CR00317833 | 기타 | etc | 1 |
| PJ200109.0012053 | CR00317833 | 전기(통신) | Electrical | 2 |
| PJ200109.0012053 | CR00317833 | 토목 | Civil | 3 |
| PJ200109.0012053 | CR00318187 | 기타 | Etc | 1 |
4. 결언
이번에는 ROW를 COLUMN으로 변환방법을 정리해보았습니다. 워낙 많이 사용하는 기술이다 보니 이미 알고 있는 분들도 많을거라 생각합니다.