개발하면서 많이 사용하는 부서 사용자 쿼리 입니다.

;WITH TREECTE(DEPTID, PDEPTID, DEPTNM, SORT, [Level], GUBUN)
 AS
 (
  SELECT
   DEPTID
   , ISNULL(PDEPTID, 0) AS PDEPTID
   , DEPTNM
   , CONVERT(VARCHAR, DEPTID) AS SORT
   , 1 AS [Level]
   , 'D' AS GUBUN
  FROM Tbl_DEPT
  WHERE PDEPTID IS NULL
  
  UNION ALL
  
  SELECT
   C.DEPTID
   , ISNULL(C.PDEPTID, 0) AS PDEPTID
   , C.DEPTNM
   , CONVERT(VARCHAR, P.SORT + ':' + CONVERT(VARCHAR, C.DEPTID)) AS SORT
   , P.[Level] + 1 AS [Level]
   , 'D' AS GUBUN
  FROM Tbl_DEPT AS C
   INNER JOIN TREECTE AS P ON C.PDEPTID = P.DEPTID

  UNION ALL

  SELECT
   CU.CUSRUID AS DEPTID
   , ISNULL(C.PDEPTID, 0) AS PDEPTID
   , CU.CUSRNAM AS DEPTNM
   , CONVERT(VARCHAR, P.SORT + ':' + CONVERT(VARCHAR, C.DEPTID) + ':' + CU.CUSRUID) AS SORT
   , P.[Level] + 2 AS [Level]
   , 'C' AS GUBUN
  FROM Tbl_DEPT AS C
   INNER JOIN TREECTE AS P ON C.PDEPTID = P.DEPTID
   INNER JOIN Tbl_CUSR AS CU ON C.DEPTID = CU.DEPTID
 )

 SELECT
  *
 FROM TREECTE
 ORDER BY SORT

개발하면서 많이 사용하는 부서 사용자 쿼리 입니다.

;WITH TREECTE(DEPTID, PDEPTID, DEPTNM, SORT, [Level], GUBUN)
 AS
 (
  SELECT
   DEPTID
   , ISNULL(PDEPTID, 0) AS PDEPTID
   , DEPTNM
   , CONVERT(VARCHAR, DEPTID) AS SORT
   , 1 AS [Level]
   , 'D' AS GUBUN
  FROM Tbl_DEPT
  WHERE PDEPTID IS NULL
  
  UNION ALL
  
  SELECT
   C.DEPTID
   , ISNULL(C.PDEPTID, 0) AS PDEPTID
   , C.DEPTNM
   , CONVERT(VARCHAR, P.SORT + ':' + CONVERT(VARCHAR, C.DEPTID)) AS SORT
   , P.[Level] + 1 AS [Level]
   , 'D' AS GUBUN
  FROM Tbl_DEPT AS C
   INNER JOIN TREECTE AS P ON C.PDEPTID = P.DEPTID

  UNION ALL

  SELECT
   CU.CUSRUID AS DEPTID
   , ISNULL(C.PDEPTID, 0) AS PDEPTID
   , CU.CUSRNAM AS DEPTNM
   , CONVERT(VARCHAR, P.SORT + ':' + CONVERT(VARCHAR, C.DEPTID) + ':' + CU.CUSRUID) AS SORT
   , P.[Level] + 2 AS [Level]
   , 'C' AS GUBUN
  FROM Tbl_DEPT AS C
   INNER JOIN TREECTE AS P ON C.PDEPTID = P.DEPTID
   INNER JOIN Tbl_CUSR AS CU ON C.DEPTID = CU.DEPTID
 )

 SELECT
  *
 FROM TREECTE
 ORDER BY SORT