데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

WITH t AS

(

SELECT '201106' yymm, '9545607' id, '단위농협' name, '9730000S' upperid, '50' levlel FROM dual

UNION ALL SELECT '201106', '9545609','중앙농협', '9730000S', '50' FROM dual

UNION ALL SELECT '201106', '9730000S', '가농협', '97100P' , '40' FROM dual

UNION ALL SELECT '201106', '9545000' , '나농협', '97200S' , '40' FROM dual

UNION ALL SELECT '201106', '97200S' , '농협' , '97600' , '30' FROM dual

UNION ALL SELECT '201106', '97100P' , '농협' , '97600' , '30' FROM dual

UNION ALL SELECT '201106', '97600' , '파트' , '9630' , '20' FROM dual

UNION ALL SELECT '201106', '9630' , '1단' , '963' , '10' FROM dual

UNION ALL SELECT '201106', '963' , '루트' , '' , '00' FROM dual

)

SELECT yymm, id, name, upperid, levlel

, REGEXP_SUBSTR(ids, '[^-]+', 1, 1) id_00

, REGEXP_SUBSTR(nms, '[^-]+', 1, 1) nm_00

, REGEXP_SUBSTR(ids, '[^-]+', 1, 2) id_10

, REGEXP_SUBSTR(nms, '[^-]+', 1, 2) nm_10

, REGEXP_SUBSTR(ids, '[^-]+', 1, 3) id_20

, REGEXP_SUBSTR(nms, '[^-]+', 1, 3) nm_20

, REGEXP_SUBSTR(ids, '[^-]+', 1, 4) id_30

, REGEXP_SUBSTR(nms, '[^-]+', 1, 4) nm_30

, REGEXP_SUBSTR(ids, '[^-]+', 1, 5) id_40

, REGEXP_SUBSTR(nms, '[^-]+', 1, 5) nm_40

, REGEXP_SUBSTR(ids, '[^-]+', 1, 6) id_50

, REGEXP_SUBSTR(nms, '[^-]+', 1, 6) nm_50

FROM (

SELECT yymm, id, name, upperid, levlel

, SYS_CONNECT_BY_PATH(id , '-') ids

, SYS_CONNECT_BY_PATH(name, '-') nms

FROM t

START WITH yymm = '201106'

AND levlel = '00'

CONNECT BY PRIOR yymm = yymm

AND PRIOR id = upperid

)

;

마농(manon94)님이 2011-07-27 09:30에 작성한 댓글입니다.

아직 해결이 안되셨나보네요...

원하는 결과가 이런건가요?..

WITH a AS

(SELECT '201106' yymm, '9545607' ID, '단위농협' NAME, '9730000S' upperid,

'50' levlel

FROM DUAL

UNION ALL

SELECT '201106', '9545609', '중앙농협', '9730000S', '50'

FROM DUAL

UNION ALL

SELECT '201106', '9730000S', '가농협', '97100S', '40'

FROM DUAL

UNION ALL

SELECT '201106', '9545000', '나농협', '97200S', '40'

FROM DUAL

UNION ALL

SELECT '201106', '97200S', '농협', '97600', '30'

FROM DUAL

UNION ALL

SELECT '201106', '97100S', '농협', '97600', '30'

FROM DUAL

UNION ALL

SELECT '201106', '97100P', '농협', '97600', '30'

FROM DUAL

UNION ALL

SELECT '201106', '97600', '파트', '9630', '20'

FROM DUAL)

SELECT max(decode(level,1,id)) id1, max(decode(level,1,name)) name1, max(decode(level,1,upperid)) upperid1, max(decode(level,1,levlel)) levlel1,

max(decode(level,2,id)) id2, max(decode(level,2,name)) name2, max(decode(level,2,upperid)) upperid2, max(decode(level,2,levlel)) levlel2,

max(decode(level,3,id)) id3, max(decode(level,3,name)) name3, max(decode(level,3,upperid)) upperid3, max(decode(level,3,levlel)) levlel3,

max(decode(level,4,id)) id4, max(decode(level,4,name)) name4, max(decode(level,4,upperid)) upperid4, max(decode(level,4,levlel)) levlel4

FROM a

CONNECT BY PRIOR upperid=ID

START with id='9545607'

데이터는 중간에 끊기는 부분이 있어서 일부 수정했습니다...

1님이 2011-07-27 09:53에 작성한 댓글입니다. Edit Ｘ