가로를 세로로 바꾸는 SQL 작성 방법

가로를 세로로 바꾸는 SQL 작성 방법

Advanced Oracle 2008/02/28 10:00

오동규 님의 블로그에 세로로 출력되는 데이터를 가로로 출력하는 몇 가지 재미있는 기법들 소개되고 있다.

즉 아래와 같은 데이터를

SELECT DEPTNO, ENAME
FROM EMP;

DEPTNO ENAME
------ ----------
10 CLARK
10 KING
10 MILLER
20 SMITH
20 ADAMS
20 FORD
20 SCOTT
20 JONES
30 ALLEN
30 BLAKE
30 MARTIN
30 JAMES
30 TURNER
30 WARD

아래와 같이 출력하기를 원한다.

DEPTNO ENAME
------- ------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

이런 요구사항은 매우 빈번하고 또 일반적이다.

이 블로그에서는 Oracle, MySQL, SQL Server 등에서 이런 출력을 가능하게 하는 다양한 방법을 소개하고 있다. Oracle에서 사용 가능한 방법은 다음과 같이 소개되어 있다.

select deptno,
ltrim(sys_connect_by_path(ename,','),',') ename
from (
select deptno,
ename,
row_number() over (partition by deptno order by empno) rn,
count(*) over (partition by deptno) cnt
from emp
)
where level = cnt
start with rn = 1
connect by prior deptno = deptno and prior rn = rn-1 ;

analytic function과 connect by를 적절히 이용하는 강력한 기법이다. (MySQL는 간단한 함수 하나로 이것을 할 수 있다는게 부러울 따름이다!)

이에 덧붙여 Oracle에서 비슷한 효과를 낼 수 있는 간단한 기법 하나를 소개하고자 한다.
간단한 기법이란 아래와 같이 Cursor 함수를 사용하는 것이다.

SCOTT@ukja10> select
2 e.deptno, cursor(select ename from emp where deptno = e.deptno)
3 from emp e
4 group by e.deptno
5 ;

출력 결과는 다음과 같다.

DEPTNO CURSOR(SELECTENAMEFR
---------- --------------------
30 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME
------------------------------
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES

6 rows selected.

20 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME
------------------------------
SMITH
JONES
SCOTT
ADAMS
FORD

10 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME
------------------------------
CLARK
KING
MILLER

엄밀하게 말하면 가로로 출력하는게 아니라 Cursor를 컬럼처럼 리턴받아서 이 Cursor에 대해 Fetch를 하는 방식이다. JDBC 등에서 이를 출력받아서 Fetch 하면서 디스플레이하면 마치 가로로 출력한 것과 동일한 효과를 얻을 수 있다. 이 방법의 장점 하나는 매우 복잡한 요구 사항도 쉽게 처리가 가능하다는 것이다.