WITH test AS
(
SELECT 'A' code FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'C' FROM dual
)
SELECT SubStr(sys_connect_by_path(code,'-'),2) cod FROM test
CONNECT BY PRIOR code < code
ORDER BY Length(cod),code

아 두번째 경우의 수는 아무리 머리를 짜내도
안되겠네요 ㅠ.ㅠ grouping sets를 사용하는건 답이 아닌 것 같고...
connect by 로 구해야 할것 같은데
답을 살짝 봤는데 nocycle은 9i에서는 안되는 것 같고.. ㅠ.ㅠ
좌절....

죄송합니다. 2번답은 10G 버전용 답입니다.

1번

WITH test AS
(
SELECT 'A' code FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'C' FROM dual
)
SELECT
LEVEL
, CODE
, SUBSTR(SYS_CONNECT_BY_PATH(CODE,'-'),2) AS RN
FROM TEST
CONNECT BY PRIOR CODE < CODE
ORDER BY LEVEL, RN

쪼렙입니다..

마농님??

9i에서는 안되는건가영??

1번은.... 흐흐흐 저번에 풀었던 거구요..
2번은..-0-; 아.. 풀긴 풀었는데 9I 로 할려니 ROOT 도 없고. NOCYCLE 도 없고
-_-; 쩝.. 야매로 풀었어요.ㅎㅎ^ㅡ^: 세미나 ??뵈요

SELECT RESULT
FROM(SELECT SUBSTR(SYS_CONNECT_BY_PATH(CODE,'-'),2) RESULT
FROM TEST, (SELECT ROWNUM RN FROM DUAL CONNECT BY LEVEL<=3)
START WITH RN=1
CONNECT BY PRIOR CODE <> CODE AND PRIOR RN = RN-1)
WHERE NVL(SUBSTR(RESULT,1,INSTR(RESULT,'-',1)-1),' ')<>SUBSTR(RESULT,INSTR(RESULT,'-',-1)+1)
ORDER BY LENGTH(RESULT),SUBSTR(RESULT,1,1)

오호~ 보완해야할 점이 보이긴 하지만 9i에서도 가능성이 보이는군..

호야님의 힌트로 9i 버전용 쿼리를 만들어 봤습니다.
WITH test AS
(
SELECT 'A' code FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'C' FROM dual
--UNION ALL SELECT 'D' FROM dual
)
, copy_t AS
(
SELECT ROWNUM rn FROM test
)
SELECT SUBSTR(code,2) code
FROM
(
SELECT LEVEL lv
, SYS_CONNECT_BY_PATH(code,'-') code
FROM test, copy_t
START WITH rn = 1
CONNECT BY PRIOR code != code
AND PRIOR rn + 1 = rn
)
, copy_t
WHERE rn <= lv
GROUP BY lv, code
HAVING COUNT(DISTINCT SUBSTR(code
, INSTR(code,'-',1,rn)+1
, INSTR(code||'-','-',2,rn) - INSTR(code,'-',1,rn) - 1)
) = lv
ORDER BY lv, code
;

ㅠ..ㅠ 어버어버버버버... 아무리 분석해도 먼말인지 모르게써용..
값도 안 나오궁...ㅠ..ㅠ 흑.. count 했는넘이 1이고.. lv는 1~4정도 나와서
결과 셋은 a,b,c 만 리턴을 해용..-_- 먼가 중간에 꼬인듯한...
어려워용...

10G에서 테스트 해서 9i에서 안되나 보네요.

-- From절에서 copy_t의 위치를 뒤에 두니 안나오고 앞에 두니 나오네요.
-- 9i에서의 버그인듯 한데요.
WITH test AS
(
SELECT 'A' code FROM dual
UNION ALL SELECT 'B' FROM dual
UNION ALL SELECT 'C' FROM dual
UNION ALL SELECT 'D' FROM dual
)
, copy_t AS
(
SELECT ROWNUM rn FROM test
)
SELECT SUBSTR(code,2) code
FROM copy_t,
(
SELECT LEVEL lv
, SYS_CONNECT_BY_PATH(code,'-') code
FROM test, copy_t
START WITH rn = 1
CONNECT BY PRIOR code != code
AND PRIOR rn + 1 = rn
)
WHERE rn <= lv
GROUP BY lv, code
HAVING COUNT(DISTINCT SUBSTR(code
, INSTR(code,'-',1,rn)+1
, INSTR(code||'-','-',2,rn) - INSTR(code,'-',1,rn) - 1)
) = lv
ORDER BY lv, code
;

아...-_- 이런... ^ㅡ^ 역시 마농형님...
ㅠ..ㅠ 형님이 사수 였으면 엄청 많이 배울텐데..-_-
나의 사수는 언제쯤 찾을 까.... 킁

두분 쩌는군영 ;;;;


CONNECT BY PRIOR code != code
AND PRIOR rn + 1 = rn

이부분 보강이 필요하다구 봅니다. 전 .... +_+

주옥같은 설명좀 부탁드립니다. ㅠ,.ㅠ

분석은.....루ㅡㅁ;ㅣ룸로ㅔㅐㅁㄴ;ㅔㄹㄴㅁ런;ㄹ

전 두번째 connect by 안쓰고 풀어봤습니당...
select distinct rtrim(a.code || '-' ||
decode(b.code, a.code,'', b.code || '-') ||
decode(c.code, a.code,'', b.code,'', c.code), '-') code
from test a, test b, test c
order by length(code), code