오라클이 아닌 MSSQL 기준으로 작성되었습니다.

오라클에서는 Connect By 라는게 있다던데 안써봐서 ^^;;

오라클에서도 CTE 는 가능하니 조금 수정하면 사용할수 있지 않을까 싶네요.

구문이 다른건 조금 수정하셔야 할 겁니다.

하기쿼리 참고하세요.

---쿼리시작---

;with tblA As
(
SELECT '001' id, '초등학교' menuname, '*' 부모id, 'N' 노출YN FROM tdual
UNION ALL SELECT '002', '1학년' , '001', 'Y' FROM tdual
UNION ALL SELECT '003', '1반' , '002', 'N' FROM tdual
UNION ALL SELECT '004', '2반' , '002', 'Y' FROM tdual
UNION ALL SELECT '005', '3반' , '002', 'N' FROM tdual
UNION ALL SELECT '006', '2학년' , '001', 'N' FROM tdual
UNION ALL SELECT '007', '1반' , '006', 'N' FROM tdual
UNION ALL SELECT '008', '2반' , '006', 'N' FROM tdual
UNION ALL SELECT '009', '3반' , '006', 'Y' FROM tdual
UNION ALL SELECT '101', '중학교', '*', 'N' FROM tdual
UNION ALL SELECT '102', '1학년' , '101', 'Y' FROM tdual
UNION ALL SELECT '103', '1반' , '102', 'Y' FROM tdual
UNION ALL SELECT '104', '2반' , '102', 'N' FROM tdual
UNION ALL SELECT '105', '3반' , '102', 'N' FROM tdual
)
,tblB (id,menuname,부모id,노출YN,leve,serial,sort,item,name) As
(
    Select *
          ,1
          ,CONVERT(varchar(max),'1')
          ,ROW_NUMBER() Over (PARTITION By a.menuname,a.부모id Order By a.menuname,a.부모id,a.id)
          ,a.menuname
          ,Convert(varchar(max),a.menuname)
      From tblA a
     Where a.부모id = '*'
    
 Union All
 
    Select a.id
          ,a.menuname
          ,a.부모id
          ,a.노출YN
          ,b.leve + 1
          ,CONVERT(varchar(max),Convert(varchar(max),b.serial) + Convert(varchar(max),b.leve+1))
          ,ROW_NUMBER() Over (PARTITION By b.item,a.부모id Order By b.item,a.부모id,a.id)
          ,b.item   
          ,Convert(varchar(max),b.name + '-' + a.menuname )    
      From tblA a
                  Inner Join tblB b On a.부모id = b.id
)
,tblC (id,menuname,부모id,노출YN,leve,serial,sort,item,name,seq) As
(
   Select a.*
         ,(
               Case When ISNULL(b.sort,'') = ''
                       Then '1'
                    Else Replicate('1',a.leve -2 ) + Convert(varchar(10),b.sort) + Convert(varchar(10),a.sort)
               End
          )
     From tblB a Left Outer Join tblB b On a.부모id = b.id
)
,tblD (id,menuname,부모id,노출YN,leve,serial,sort,item,name,seq) As
(
    Select a.*
      From tblC a
     Where a.노출YN = 'Y'

 Union All    
 
    Select b.*
      From tblD a
                  Inner Join tblC b On a.부모id = b.id
)
    Select a.id
          ,MAX(a.item)  As item
          ,MAX(a.name)  As name
          ,MAX(a.menuname) As menuname
          ,a.부모id       As 부모id
          ,Max(a.노출YN)  As 노출YN
          ,MAX(a.leve)    As leve
          ,MAX(a.sort)    As seq
          ,a.seq          As serial
      From tblD a
  Group By
           a.id
          ,a.부모id
          ,a.seq
  Order By
           MAX(a.item) asc
          ,a.seq Asc

---쿼리끝---

SELECT SUBSTR(SYS_CONNECT_BY_PATH(menuname, ' - '), 4) result
  FROM t
 WHERE 노출YN = 'Y'
 START WITH 부모id = '*'
 CONNECT BY PRIOR id = 부모id
;