ID | P_ID | LEVEL | 이름 |
M | NULL | 0 | TOP |
M1 | M | 1 | 소프트웨어 |
M11 | M1 | 2 | 웹 |
M111 | M11 | 3 | php |
M112 | M11 | 3 | jsp |
M113 | M11 | 3 | html |
M12 | M1 | 2 | 운영체제 |
M13 | M1 | 2 | 윈도우어플리케이션 |
M2 | M | 1 | 하드웨어 |
데이터는 위와 같이 되어 있구요..
결과는 아래와 같이 나오야 하는데요...
connect by 를 써야하는거 알겠는데 level3 부터는 쉼표로 나타내야한다는게 참 어렵네요.. 혹시 어떻게 쿼리를 짜야 할지 도움좀 주세요...
LEVEL | 이름 |
1 | 소프트웨어 |
2 | 웹(php,jsp,html) |
2 | 운영체제 |
2 | 윈도우어플리케이션 |
1 | 하드웨어 |
WITH T AS ( SELECT 'M' ID ,NULL P_ID ,0 LV ,'TOP' NM FROM DUAL UNION ALL SELECT 'M1' ,'M' ,1 ,'소프트웨어' FROM DUAL UNION ALL SELECT 'M11' ,'M1' ,2 ,'웹' FROM DUAL UNION ALL SELECT 'M111' ,'M11' ,3 ,'php' FROM DUAL UNION ALL SELECT 'M112' ,'M11' ,3 ,'jsp' FROM DUAL UNION ALL SELECT 'M113' ,'M11' ,3 ,'html' FROM DUAL UNION ALL SELECT 'M12' ,'M1' ,2 ,'운영체제' FROM DUAL UNION ALL SELECT 'M13' ,'M1' ,2 ,'윈도우어플리케이션' FROM DUAL UNION ALL SELECT 'M2' ,'M' ,1 ,'하드웨어' FROM DUAL ) SELECT MIN(LV),REPLACE(LISTAGG(NM,',') WITHIN GROUP (ORDER BY ID),',(','(') NM FROM ( SELECT ID, P_ID, LV, NM , ID2 FROM( SELECT MAX(ID) ID, MAX(P_ID) P_ID, MAX(LV) LV , DECODE(MAX(LV),3,'(','')||LISTAGG(NM,',') WITHIN GROUP(ORDER BY P_ID)||DECODE(MAX(LV),3,')','') NM , DECODE(MAX(LV),3,SUBSTR(MAX(ID),1,3),MAX(ID)) ID2 FROM T GROUP BY DECODE(LV,3,LV,ROWNUM-1000) ) START WITH P_ID = 'M' CONNECT BY PRIOR ID = P_ID ) GROUP BY ID2
결과값은 나오는데.. 그닥 좋은 방법은 아닌듯...
-- jkson님 WITH문 사용했습니다. WITH T AS ( SELECT 'M' ID ,NULL P_ID ,0 LV ,'TOP' NM FROM DUAL UNION ALL SELECT 'M1' ,'M' ,1 ,'소프트웨어' FROM DUAL UNION ALL SELECT 'M11' ,'M1' ,2 ,'웹' FROM DUAL UNION ALL SELECT 'M111' ,'M11' ,3 ,'php' FROM DUAL UNION ALL SELECT 'M112' ,'M11' ,3 ,'jsp' FROM DUAL UNION ALL SELECT 'M113' ,'M11' ,3 ,'html' FROM DUAL UNION ALL SELECT 'M12' ,'M1' ,2 ,'운영체제' FROM DUAL UNION ALL SELECT 'M13' ,'M1' ,2 ,'윈도우어플리케이션' FROM DUAL UNION ALL SELECT 'M2' ,'M' ,1 ,'하드웨어' FROM DUAL ) SELECT T.LV, T.NM||AA.NM NM FROM T , (SELECT P_ID , MAX(LV) LV , '('||SUBSTR(XMLAgg(XMLElement(x, ',', NM) ORDER BY ID).Extract('//text()'), 2)||')' NM FROM T GROUP BY P_ID ) AA WHERE T.LV BETWEEN 1 AND 2 AND T.ID = AA.P_ID(+) AND AA.LV(+) = 3 ORDER BY T.ID ;
-- jkson님 WITH문 사용했습니다.
WITH T AS
(
SELECT 'M' ID ,NULL P_ID ,0 LV ,'TOP' NM FROM DUAL UNION ALL
SELECT 'M1' ,'M' ,1 ,'소프트웨어' FROM DUAL UNION ALL
SELECT 'M11' ,'M1' ,2 ,'웹' FROM DUAL UNION ALL
SELECT 'M111' ,'M11' ,3 ,'php' FROM DUAL UNION ALL
SELECT 'M112' ,'M11' ,3 ,'jsp' FROM DUAL UNION ALL
SELECT 'M113' ,'M11' ,3 ,'html' FROM DUAL UNION ALL
SELECT 'M12' ,'M1' ,2 ,'운영체제' FROM DUAL UNION ALL
SELECT 'M13' ,'M1' ,2 ,'윈도우어플리케이션' FROM DUAL UNION ALL
SELECT 'M2' ,'M' ,1 ,'하드웨어' FROM DUAL
)
SELECT LV, T.NM||T2.NM
FROM T, (
select SUBSTR(ID,1,3) ID,'('||LISTAGG(NM,',') WITHIN GROUP (ORDER BY ID)||')' NM
from t
where lv = 3
GROUP BY SUBSTR(ID,1,3)) T2
WHERE T.ID = T2.ID(+)
AND LV BETWEEN 1 AND 2
ORDER BY T.ID;