아래와 같은 임시 테이블(임시VIEW)의 정보를 하나의 ac에 대해서 속해있는 acv 값을 하나의 string로 나열하기.
WITH TVIEW AS (
SELECT 'a' AS ac , '1a' AS acv FROM dual UNION ALL
SELECT 'a' AS ac , '3a' AS acv FROM dual UNION ALL
SELECT 'e' AS ac , '2e' AS acv FROM dual UNION ALL
SELECT 'a' AS ac , '4a' AS acv FROM dual UNION ALL
SELECT 'b' AS ac , '1b' AS acv FROM dual UNION ALL
SELECT 'c' AS ac , '1c' AS acv FROM dual UNION ALL
SELECT 'g' AS ac , '2g' AS acv FROM dual UNION ALL
SELECT 'c' AS ac , '2c' AS acv FROM dual UNION ALL
SELECT 'c' AS ac , '3c' AS acv FROM dual UNION ALL
SELECT 'a' AS ac , '2a' AS acv FROM dual UNION ALL
SELECT 'd' AS ac , '1d' AS acv FROM dual UNION ALL
SELECT 'e' AS ac , '1e' AS acv FROM dual UNION ALL
SELECT 'f' AS ac , '1f' AS acv FROM dual UNION ALL
SELECT 'g' AS ac , '1g' AS acv FROM dual UNION ALL
SELECT 'g' AS ac , '3g' AS acv FROM dual UNION ALL
SELECT 'h' AS ac , '1h' AS acv FROM dual UNION ALL
SELECT 'h' AS ac , '2h' AS acv FROM dual UNION ALL
SELECT 'i' AS ac , '1i' AS acv FROM dual
)
예로 다음과 같이 결과를 만들기.
ac acvl
=== ==============================
a |1a|2a|3a|4a
b |1b
c |1c|2c|3c
...
다음과 같이 쿼리를 작성하면 된다.
SELECT ac ,MAX(SYS_CONNECT_BY_PATH (acv, '|')) AS acvl
FROM (
SELECT
ac, acv
,(ROW_NUMBER () OVER (PARTITION BY ac ORDER BY ac, acv )) RNUM
FROM TVIEW
) START WITH RNUM = 1 CONNECT BY PRIOR RNUM = (RNUM - 1)
AND PRIOR AC = AC
GROUP BY AC
설명 및 테스트 결과 설명은 다음 글에서 ...
|
Oracle 유용 구분 정리 해석내용 - PARTITION BY, START WITH...CONNECT BY PRIOR (1)
|
2010/05/28 |
|
Oracle 유용 구분 정리 - PARTITION BY, START WITH...CONNECT BY PRIOR (0)
|
2010/04/22 |
|
오라클 프로그램에서 한글 깨짐 현상 바로 잡기. (0)
|
2009/06/18 |
|
java.sql.SQLException: ORA-00600: internal error code, arguments: [qesmmCValStat4], [3], [1], [], [], [], [], [] (0)
|
2009/01/28 |
This is an example of a WordPress page, you could edit this to put information about yourself or your site so readers know where you are coming from.
XHTML
혼자 주말근무 나와서 동일 메세지로 끙끙대던중
이글이 도움이 되었습니다
쿼리는 동일 방식을 사용하지만(유지보수를 위해서라나)
전 주석만 고쳐서 해결이 된 케이스네요
감사합니다