태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

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

오라클 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 하면서 디스플레이하면 마치 가로로 출력한 것과 동일한 효과를 얻을 수 있다. 이 방법의 장점 하나는 매우 복잡한 요구 사항도 쉽게 처리가 가능하다는 것이다.

Oracle의 PL/SQL은 매우 강력해서 우리가 생각한 것보다 훨씬 많은 일을 할 수 있다. 잘 활용하면 원하는 작업을 보다 간단하게 처리할 수 있는 기회가 있을 것이다.



신고
Trackback 0 : Comments 9
  1. nokarma 2008.02.28 13:03 신고 Modify/Delete Reply

    개발자 입장에서 볼 때, 이걸 어디다가 써먹으면 좋을지 얼른 와닫지 않는군요.
    데이타 포맷팅은 어플리케이션 프로그램에서 하지 왜 데이타베이스 CPU돌려가면서 해야할까란 생각이 먼저 듭니다.

    SQL/PLUS 주로 쓰는 DBA들에게는 유용한 테크닉인가 보군요.

  2. 욱짜 2008.02.28 13:53 신고 Modify/Delete Reply

    문제를 해결하는데 생각보다 다양한 기법들이 있다... 이렇게 이해하면 가장 정확할거 같습니다. 어떤 경우는 (가령) Java에서, 어떤 경우에는 Database에서 하는 것이 더 유리한 경우가 있을 것이고, 이런 다양한 기법들을 이해하면 적절한 때에 적절한 기법을 쓸 수 있을 걸로 생각됩니다.

  3. oracler 2008.03.21 22:50 신고 Modify/Delete Reply

    데이타 포매팅은 application 에서 할수도 있지만
    반드시 SQL 에서 직접 해야 하는 경우도 있을수 있고
    이런 저런 다양한 기법을 알아놓는것은 분명 도움이 됩니다.

    잊을만 하면 툭 튀어나와 그때마다 필요로 하는것이 가로 세로 바꾸는것이고
    이런 TIP 류의 가려운점때문에 전세계 오라클 관련 개발자들이 시간낭비하고 있는데
    아직까지 공식적으로 쉬운 솔루션을 제공하지 않는것이 참 이상합니다.
    볼때마다 새롭고 볼때마다 헷갈려요..ㅎㅎ

  4. 욱짜 2008.04.01 22:15 신고 Modify/Delete Reply

    좋은 정보 감사합니다. 언제 사용법을 블로그에 올려주시면 여러 사람이 도움을 받을 수 있겠습니다. ^^

  5. oracler 2008.04.02 23:03 신고 Modify/Delete Reply

    pivot / unpivot 기능은 아래 :
    http://blog.naver.com/kittynbu?Redirect=Log&logNo=120048945452

  6. 욱짜 2008.04.16 09:46 신고 Modify/Delete Reply

    http://www.oracle-developer.net/display.php?id=506
    11g의 pivot/unpivot에 대해 가장 상세하고 정확한 설명이 올라왔네요. 확인해보시기 바랍니다.

  7. 오동규 2008.10.03 23:51 신고 Modify/Delete Reply

    Oracle 11g 부터 pivot 과 unpivot 기능이 추가 되었습니다. 참고 하세요^^

  8. 머여 2008.11.19 13:52 신고 Modify/Delete Reply

    머여 이게 가로를 세로로 만든거여 세로를 가로로 만든거구만

  9. 욱짜 2008.11.20 10:14 신고 Modify/Delete Reply

    ㅎㅎ 그러네요.

Write a comment

티스토리 툴바