MS-SQL 계층형 쿼리

조직 트리 데이타 조회 예시

WITH CTE_TABLE(DEPT_CD, DEPT_NM, UP_DEPT_CD, LVL, SORT, DEPT_FULL_NM)
			AS
			(
				SELECT DEPT_CD
				     , DEPT_NM
				     , UP_DEPT_CD
				     , 1 AS LVL
				     , CONVERT (NVARCHAR(200), DEPT_CD) AS SORT
				     , CONVERT (NVARCHAR(200), DEPT_NM) AS DEPT_FULL_NM
				 FROM EC_DEPT
				 WHERE UP_DEPT_CD = 'ROOT' --최상위부서
				 UNION ALL
				SELECT A.DEPT_CD
				     , A.DEPT_NM
				     , A.UP_DEPT_CD
				     , cte.LVL + 1 AS LVL
				     , CONVERT (NVARCHAR(200), CONVERT(NVARCHAR(200), cte.SORT) + '>' + CONVERT(NVARCHAR(200), A.DEPT_CD)) AS SORT
				     , CONVERT (NVARCHAR(200), CONVERT(NVARCHAR(200), cte.DEPT_FULL_NM) + '>' + CONVERT (NVARCHAR(200), A.DEPT_NM)) AS DEPT_FULL_NM
			      FROM EC_DEPT A INNER JOIN CTE_TABLE cte ON A.UP_DEPT_CD = cte.DEPT_CD 
			)
			SELECT DEPT_CD      as deptCd
			     , DEPT_NM      as deptNm
			     , UP_DEPT_CD   as upDeptCd
			     , LVL          as lvl
			     , SORT         as sort
			     , DEPT_FULL_NM as deptFullNm
			  FROM CTE_TABLE
			 ORDER BY SORT

MS-SQL 재귀쿼리 관련 정보

 

https://eehoeskrap.tistory.com/76

[SQLD] 23. 계층형 질의와 셀프 조인