5 중 1 2007-06-12 오후 5:52 Home Login Register SQL Query SQL Tuning Oracle Administration Tools References Boards SoQooL? 쏘쿨 SoQooL) 이란? Q&A Tips Lectures Function Lectures Oracle Spatial Tips Scripts SQL Design Patterns Main Links: 사는얘기 SQL 쿼리팁 SQL 쿼리질문과답변 Toad 팁 Toad 강좌 오라클함수강좌 Execution Plan 정리 스크립트모음 Search This Category: Go 목록으로 Back to Thread 답글쓰기 Replay to this Thre Replies: 3 - Last Post: 2007-03-25 01:43:19 by 김홍선 ) - [ Previous Next ] 다중 Row 결과를단일행으로컴마로분리해출력하는방법 2) Posted: 2006-05-15 11:47:19) 글쓴이 : 김홍선 * 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
5 중 2 2007-06-12 오후 5:52 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 번예제의쿼리에서추가된부분을눈여겨보자.
5 중 3 2007-06-12 오후 5:52 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) 새창으로 문제 ) 특정기간안에있는모든요일을한방쿼리로구할수가있나요? 즉 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
5 중 4 2007-06-12 오후 5:52 DT ------- 화, 수, 목 RE: 다중 Row 결과를단일행으로컴마로분리해출력하는방법 2) Posted: 2006-08-11 21:19:29) 새창으로 주제와는관련이없지만, 바로위의문제를다른분의아이디어를빌려좀더간단히만들어봤습니다. 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) 새창으로 최초에제시한두개의쿼리들에대해서, 오라클개발자인 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)
5 중 5 2007-06-12 오후 5:52 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 Replies: 3 - Last Post: 2007-03-25 01:43:19.0 by 김홍선 ) - [ Previous Next ] 목록으로 Back to Thread 답글쓰기 Replay to this Thre Copyright c 2006.03 SoQooL. All rights reserved. This site is running Oracle 9.2.0.4.