| 다중 Row 결과를 단일행으로 컴마로 분리해 출력하는 방법 (2) (Posted: 2006-05-15 11:47:19) |
| by 김홍선 (Posts: 1414 - Registered: 2006-04-15) | |
글쓴이 : 김홍선
* SYS_CONNECT_BY_PATH 함수는 연결하려는 문자열의 수가 많아질 경우,
해당 쿼리의 performance 에 문제가 생길 수 있습니다.
충분히 테스트하고 적용하시기 바랍니다.
SYS_CONNECT_BY_PATH 함수 사용으로 performance 에 문제가 생길 경우,
plsql로 해결하시면 되겠습니다.
예제 1)
다중 Row 결과를 단일행으로 컴마로 분리해 출력하는 방법(1) 에서,
만약 행들이 그룹번호에 따라 그룹으로 나뉘어져 있고,
각 그룹별로 하나의 행으로 컴마로 분리해 출력하려고 하면 어떻게 해야 할 것인가?
이름 그룹
==========
홍길동 1
김길동 1
이길동 1
홍순신 2
김순신 2
이순신 2
==========
-->
그룹 이름
======================
1 홍길동,김길동,이길동
2 홍순신,김순신,이순신
======================
emp.ename 컬럼, 그리고 그룹번호로 emp.deptno 컬럼을 예로 들어 쿼리를 구성해 보자.
emp 테이블의 deptno, ename 컬럼은 아래와 같다.
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD
쿼리와 쿼리 결과는 아래와 같다.
쿼리에서 정확히 어떤 컬럼들이 어떤 역할을 하는지 이번에도 숙지하도록 하자.
SELECT deptno, SUBSTR (MAX (SYS_CONNECT_BY_PATH (ename, ',')), 2) path#
FROM (SELECT ename, deptno,
ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY ename) rnum
FROM emp)
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR deptno = deptno
GROUP BY deptno
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
예제 2)
이번에는 deptno 와 같이 그룹으로 나뉘는 컬럼이 2개(deptno,job) 일 때를 고려해 보자. (group by deptno, job)
3개 이상도 비슷한 방법으로 해결한다.
즉 아래의 테이블을,
10 CLERK MILLER
10 MANAGER CLARK
10 PRESIDENT KING
---------------------------
20 ANALYST FORD
20 ANALYST SCOTT
20 CLERK ADAMS
20 CLERK SMITH
20 MANAGER JONES
---------------------------
30 CLERK JAMES
30 MANAGER BLAKE
30 SALESMAN ALLEN
30 SALESMAN MARTIN
30 SALESMAN TURNER
30 SALESMAN WARD
아래와 같이 출력이 되도록 쿼리를 만들어 보자.
10 CLERK MILLER
10 MANAGER CLARK
10 PRESIDENT KING
20 CLERK ADAMS,SMITH
20 ANALYST FORD,SCOTT
20 MANAGER JONES
30 CLERK JAMES
30 MANAGER BLAKE
30 SALESMAN ALLEN,MARTIN,TURNER,WARD
쿼리는 아래와 같다.
1번 예제의 쿼리에서 추가된 부분을 눈여겨 보자.
SELECT deptno, job,
SUBSTR (MAX (SYS_CONNECT_BY_PATH (ename, ',')), 2) path#
FROM (SELECT deptno, job, ename,
ROW_NUMBER () OVER (PARTITION BY deptno, job ORDER BY ename) rnum
FROM emp)
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR deptno = deptno AND PRIOR job = job
GROUP BY deptno, job
* 글쓴이 : 김홍선
* 위 내용을 이 곳에서 처음 보신분은 다른 곳에 게재하실 때 반드시 출처를 밝혀주시기 바랍니다.
* 위 내용에 관해서 잘못된 부분이 있거나 질문이 있으신 분은 답글로 알려주시기 바랍니다.
|
| |
| RE: 다중 Row 결과를 단일행으로 컴마로 분리해 출력하는 방법 (2) (Posted: 2006-08-09 16:43:25) 새창으로 |
| by 김홍선 (Posts: 1414 - Registered: 2006-04-15) | |
문제)
특정기간안에 있는 모든 요일을 한방쿼리로 구할수가 있나요?
즉 2006.08.01 ~ 2005.08.03기간의 요일은 '화,수,목' 이쟎아요
이걸 '화,수,목' 이렇게 리턴하게 쿼리로 구할수가 있는건지 궁금합니다.
시작일부터 종료일 까지 모든 요일이 시작일을 기준으로 보여주면 되구요, 구분은 콤마(,)로 나오게 하려구요. 중복은 제거하지 않아도 돼구요..
답변)
언뜻 보기에 connect by prior 절이 들어가는 hierarchical query로 쉽게 풀릴거라는 생각이 든다.
하지만, connect by level 구문만을 써서 색다르게 결과를 만드는 쿼리를 아래와 같이 시도해 보자.
SELECT SUBSTR
(
MAX
(
SYS_CONNECT_BY_PATH
(
TO_CHAR (start_dt + LEVEL - 1, 'dy'), ','
)
), 2
) dt
FROM (SELECT TO_DATE ('2006.08.01', 'yyyy.mm.dd') start_dt
, TO_DATE ('2006.08.03', 'yyyy.mm.dd') end_dt
FROM DUAL)
CONNECT BY start_dt + LEVEL - 1 <= end_dt
DT
-------
화,수,목
|
| |
| RE: 다중 Row 결과를 단일행으로 컴마로 분리해 출력하는 방법 (2) (Posted: 2006-08-11 21:19:29) 새창으로 |
| by 김홍선 (Posts: 1414 - Registered: 2006-04-15) | |
주제와는 관련이 없지만, 바로 위의 문제를 다른 분의 아이디어를 빌려
좀 더 간단히 만들어 봤습니다.
SELECT SUBSTR (LPAD (str, (NEXT_DAY (e_dt, 1) - TRUNC (s_dt, 'd')) * 3, str)
, TO_CHAR (s_dt, 'd') * 2 - 1
, (e_dt - s_dt) * 2 + 1
) RESULT
FROM (SELECT TO_DATE (:s_dt, 'yyyymmdd') s_dt
, TO_DATE (:e_dt, 'yyyymmdd') e_dt
, '일,월,화,수,목,금,토,' str
FROM DUAL)
|
| |
| Tom Kyte의 쿼리 (Posted: 2007-03-25 01:43:19) 새창으로 |
| by 김홍선 (Posts: 1414 - Registered: 2006-04-15) | |
최초에 제시한 두 개의 쿼리들에 대해서,
오라클 개발자인 Tom Kyte가 제시한 쿼리들을 참고용으로 남기도록 하겠습니다.
SELECT DEPTNO
, LTRIM (SYS_CONNECT_BY_PATH (ENAME, ','), ',') PATH#
FROM (SELECT ENAME
, DEPTNO
, ROW_NUMBER () OVER (PARTITION BY DEPTNO ORDER BY ENAME) RN
, COUNT (*) OVER (PARTITION BY DEPTNO) CNT
FROM EMP)
WHERE RN = CNT
START WITH RN = 1
CONNECT BY PRIOR RN = RN - 1
AND PRIOR DEPTNO = DEPTNO
SELECT DEPTNO
, JOB
, LTRIM (SYS_CONNECT_BY_PATH (ENAME, ','), ',') PATH#
FROM (SELECT DEPTNO
, JOB
, ENAME
, ROW_NUMBER () OVER (PARTITION BY DEPTNO, JOB ORDER BY ENAME) RN
, COUNT (*) OVER (PARTITION BY DEPTNO, JOB) CNT
FROM EMP)
WHERE RN = CNT
START WITH RN = 1
CONNECT BY PRIOR RN = RN - 1
AND PRIOR DEPTNO = DEPTNO
AND PRIOR JOB = JOB
아래는 위 방법을 제시한 Tom Kyte의 블로그 페이지입니다.
http://tkyte.blogspot.com/2006/08/evolution.html |
| |
출처 :
http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1010&seq=58&page=1&position=1다양한 고급 쿼리관련 사항이 많이 있는 사이트.
http://www.soqool.com
