티베로 쿼리 문의 0 4 1,508

by 가을에사랑 [2018.10.11 15:53:22]


LEVEL ID PARENTID NAME
1 0000000101 <NULL>    루트
2 0000029474 0000000101       [구2]BU
3 0000030210 0000029474          [O]D2S
4 0000030218 0000030210             Sales Environment Structure Improvement
5 0000030222 0000030218                유통POG 미적용품목
5 0000030221 0000030218              유통POG 적용품목
5 0000030220 0000030218                진열관리
5 0000030219 0000030218                진열제안
4 0000030215 0000030210             프로모션관리
5 0000030217 0000030215                매장
5 0000030216 0000030215                유통본부
3 0000029735 0000029474          [O]P2P
4 0000029974 0000029735             Vendor Management
5 0000029975 0000029974                [공통] 평가/운영/거래종료
4 0000029873 0000029735             조달구매
5 0000029937 0000029873                상품 조달
5 0000029936 0000029873                제품 조달
5 0000029919 0000029873                협력기업 원부재료 조달
2 0000021864 0000000101       Japan BU
3 0000029478 0000021864          [M]T2R
4 0000029479 0000029478             리스크대응
5 0000029505 0000029479                [On-line] 리콜
5 0000029492 0000029479                미납/미출_단기
5 0000029484 0000029479                미납/미출
5 0000029483 0000029479                사고
5 0000029480 0000029479                안전 이슈 제기
3 0000029475 0000029474          [L]S2S
4 0000029476 0000029475             Business Planning
5 0000029477 0000029476                연간사업계획수립
2 0000018818 0000000101       [구]한국BU2
3 0000019580 0000018818          [O]D2S
4 0000019585 0000019580             Management
5 0000019586 0000019585                Promotion Management
4 0000019581 0000019580             Account Management
5 0000019584 0000019581                SL
5 0000019583 0000019581                KIDS

 

레벨 계층명
2 계층1
4 항목1
2 계층2
4 항목2-1
4 항목2-2
2 계층3
4 항목3-1
4 항목3-2
4 항목3-3

 

위 데이터를 아래와 같이 나오게 하고 싶습니다. 

 

계층1       계층2        계층3     계층4....

항목1       항목2-1     항목3-1   항목4-1....

              항목2-2     항목3-2...

                              항목3-3

by 마농 [2018.10.11 16:01:25]

위 자료로는 부족한데요?
상하 관계를 알 수 있는 기준 항목이 있다던가?
그게 없다면? 위와 같이 출력되는 정렬 기준이라도 있어야 할 듯 하네요.
레벨은 2,4 두가지만 존재하나뵈요?
계층명도 "계층n"과 "항목n-n" 두가지 형태만 존재하나요?


by 가을에사랑 [2018.10.11 16:20:55]

아.. 죄송합니다.

레벨은 1부터 6까지 있지만

도식화 해야 하는 것은 2레벨을 기준으로 하는 4레벨 리스트만 보여야 해서요

그리고 모든 값마다 부모 계층값을 가지고 있습니다. ^^;;


by 마농 [2018.10.11 19:12:19]
WITH t AS
(
SELECT 1 lv, '0000000101' id, '' parentid, '루트' name FROM dual
UNION ALL SELECT 2, '0000029474', '0000000101', '[구2]BU' FROM dual
UNION ALL SELECT 3, '0000030210', '0000029474', '[O]D2S' FROM dual
UNION ALL SELECT 4, '0000030218', '0000030210', 'Sales Environment Structure Improvement' FROM dual
UNION ALL SELECT 5, '0000030222', '0000030218', '유통POG 미적용품목' FROM dual
UNION ALL SELECT 5, '0000030221', '0000030218', '유통POG 적용품목' FROM dual
UNION ALL SELECT 5, '0000030220', '0000030218', '진열관리' FROM dual
UNION ALL SELECT 5, '0000030219', '0000030218', '진열제안' FROM dual
UNION ALL SELECT 4, '0000030215', '0000030210', '프로모션관리' FROM dual
UNION ALL SELECT 5, '0000030217', '0000030215', '매장' FROM dual
UNION ALL SELECT 5, '0000030216', '0000030215', '유통본부' FROM dual
UNION ALL SELECT 3, '0000029735', '0000029474', '[O]P2P' FROM dual
UNION ALL SELECT 4, '0000029974', '0000029735', 'Vendor Management' FROM dual
UNION ALL SELECT 5, '0000029975', '0000029974', '[공통] 평가/운영/거래종료' FROM dual
UNION ALL SELECT 4, '0000029873', '0000029735', '조달구매' FROM dual
UNION ALL SELECT 5, '0000029937', '0000029873', '상품 조달' FROM dual
UNION ALL SELECT 5, '0000029936', '0000029873', '제품 조달' FROM dual
UNION ALL SELECT 5, '0000029919', '0000029873', '협력기업 원부재료 조달' FROM dual
UNION ALL SELECT 3, '0000029475', '0000029474', '[L]S2S' FROM dual
UNION ALL SELECT 4, '0000029476', '0000029475', 'Business Planning' FROM dual
UNION ALL SELECT 5, '0000029477', '0000029476', '연간사업계획수립' FROM dual
UNION ALL SELECT 2, '0000021864', '0000000101', 'Japan BU' FROM dual
UNION ALL SELECT 3, '0000029478', '0000021864', '[M]T2R' FROM dual
UNION ALL SELECT 4, '0000029479', '0000029478', '리스크대응' FROM dual
UNION ALL SELECT 5, '0000029505', '0000029479', '[On-line] 리콜' FROM dual
UNION ALL SELECT 5, '0000029492', '0000029479', '미납/미출_단기' FROM dual
UNION ALL SELECT 5, '0000029484', '0000029479', '미납/미출' FROM dual
UNION ALL SELECT 5, '0000029483', '0000029479', '사고' FROM dual
UNION ALL SELECT 5, '0000029480', '0000029479', '안전 이슈 제기' FROM dual
UNION ALL SELECT 2, '0000018818', '0000000101', '[구]한국BU2' FROM dual
UNION ALL SELECT 3, '0000019580', '0000018818', '[O]D2S' FROM dual
UNION ALL SELECT 4, '0000019585', '0000019580', 'Management' FROM dual
UNION ALL SELECT 5, '0000019586', '0000019585', 'Promotion Management' FROM dual
UNION ALL SELECT 4, '0000019581', '0000019580', 'Account Management' FROM dual
UNION ALL SELECT 5, '0000019584', '0000019581', 'SL' FROM dual
UNION ALL SELECT 5, '0000019583', '0000019581', 'KIDS' FROM dual
)
SELECT *
  FROM (SELECT lv
             , name
             , ROW_NUMBER() OVER(PARTITION BY lv, gb ORDER BY rn     ) rn
             , DENSE_RANK() OVER(PARTITION BY lv     ORDER BY gb DESC) dr
          FROM (SELECT name
                     , LEVEL  lv
                     , ROWNUM rn
                     , DECODE(LEVEL, 2, id, PRIOR parentid) gb
                  FROM t
                 WHERE LEVEL IN (2, 4)
                 START WITH parentid IS NULL
                 CONNECT BY PRIOR id = parentid
                   AND LEVEL <= 4
                 ORDER SIBLINGS BY id DESC
                )
        )
 PIVOT (MIN(name) FOR dr IN (1, 2, 3, 4))
 ORDER BY lv, rn
;

 


by 가을에사랑 [2018.10.12 13:20:19]

감사드립니다. 불금되세요 ^^

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입