재귀 쿼리로 트리 목록 한줄에 출력 레벨1>레벨2>레벨3>

트리형태로 구성된 데이터를 네비게이션처럼 계층 아래로 내려가면 현재 어떤 카테고리에서 어떤 카테고리로 이동했는지 보여줄 때 사용한다.

 

예)

level1

ㄴ level1-1

ㄴ level1-2

   ㄴ level1-2-1

   ㄴ level1-2-2

   ㄴ level1-2-3

ㄴ level1-3

leve2

ㄴ level2-1

ㄴ level2-2

ㄴ level2-3

 

 level1 > level1-2 > level1-2-3

 

테이블 설명

CodeName : 코드

HighCode  : 상위 부모 코드

 

 

WITH Tree AS ( SELECT Code , CodeName , HighCode , CONVERT(nvarchar(200), Code) AS Codes , CONVERT(nvarchar(200), CodeName) AS CodesNames FROM dbo.Common WHERE HighCode = '000000' UNION ALL SELECT a.Code , a.CodeName , a.HighCode , CONVERT(nvarchar(200), CONVERT(nvarchar(200), b.Codes) +'>' + CONVERT(nvarchar(200), a.Code)) AS Codes , CONVERT(nvarchar(200), CONVERT(nvarchar(200), b.CodesNames) +'>' + CONVERT(nvarchar(200), a.CodeName)) AS CodesNames FROM dbo.Common a, Tree b WHERE a.HighCode = b.Code ) SELECT * FROM Tree ORDER BY Codes ;

;WITH codeTbl AS ( SELECT StndItemCode , RmrkItemCode , CONVERT(VARCHAR(255), RmrkItemCode) name FROM dbo.DDETB_BA_PjtURRmrk WHERE PjtCode = @PjtCode AND EstNo = @EstNo AND BizDvs = @BizDvs AND RmrkType = @RmrkType UNION ALL SELECT A.StndItemCode , A.RmrkItemCode , CONVERT(VARCHAR(255), CONVERT(NVARCHAR,B.name) + ' > ' + CONVERT(VARCHAR(255), A.RmrkItemCode)) name FROM dbo.DDETB_BA_PjtURRmrk A , codeTbl B WHERE A.PjtCode = @PjtCode AND A.EstNo = @EstNo AND A.BizDvs = @BizDvs AND A.RmrkType = @RmrkType AND A.StndItemCode = B.RmrkItemCode ) , OnlyCode AS ( SELECT DISTINCT Code FROM ( SELECT DISTINCT StndItemCode Code FROM codeTbl UNION ALL SELECT DISTINCT RmrkItemCode Code FROM codeTbl ) A )

 

 

부서 계층

WITH tree_query 
AS ( 
    SELECT DEPT_CD
         , UP_DEPT_CD
         , DEPT_NM
         , convert(varchar(255), DEPT_CD) sort
         , convert(varchar(255), DEPT_NM) depth_fullname 
      FROM DEPT 
     WHERE UP_DEPT_CD = '' 
     UNION ALL 
     SELECT B.DEPT_CD 
          , B.UP_DEPT_CD
          , B.DEPT_NM
          , convert(varchar(255)
          , convert(nvarchar,C.sort) + ' > ' + convert(varchar(255), B.DEPT_CD)) sort
          , convert(varchar(255)
          , convert(nvarchar,C.depth_fullname) + ' > ' + convert(varchar(255), B.DEPT_NM)) depth_fullname 
       FROM DEPT B
          , tree_query C 
     WHERE B.UP_DEPT_CD = C.DEPT_CD 
) 
     
 SELECT DEPT_CD
      , UP_DEPT_CD
      , DEPT_NM
      , depth_fullname
   FROM tree_query 
  ORDER BY SORT