MSSQL에서 트리구조로 출력하기

-- 트리구조 만들기 위한 CTE 테이블 생성 예재
;
WITH Menu_CTE(MenuID, MenuName, MenuHide, MenuDllUrl, MenuClass, MenuSClass, ParentID, SortOrder)
AS
(
   SELECT A.MenuID,
          A.MenuName,
          CASE WHEN ISNULL(A.MenuHide, '0') = '0'
    THEN 'N'
    ELSE 'Y'
    END AS MenuHide,
    ISNULL(A.MenuDllUrl, '') AS MenuDllUrl,
    ISNULL(A.MenuClass, '') AS MenuClass,
    RIGHT(ISNULL(A.MenuClass, ''), 11) AS MenuSClass,
          A.ParentID,
          CONVERT(VARCHAR(20),RIGHT(CONVERT(CHAR(3),100 + A.MenuOrder),2))  AS SortOrder
   
     FROM dbo.MENUINFO AS A WITH(NOLOCK)
    where A.ParentID = '00'
   
   UNION  ALL
   SELECT A.MenuID,
          A.MenuName,
          CASE WHEN ISNULL(A.MenuHide, '0') = '0'
    THEN 'N'
    ELSE 'Y'
    END AS MenuHide,
    ISNULL(A.MenuDllUrl, '') AS MenuDllUrl,
    ISNULL(A.MenuClass, '') AS MenuClass,
    RIGHT(ISNULL(A.MenuClass, ''), 11) AS MenuSClass,
          A.ParentID,
          CONVERT(VARCHAR(20),B.SortOrder + RIGHT(CONVERT(CHAR(3),100 + A.MenuOrder),2))  AS SortOrder
   
     FROM dbo.MENUINFO AS A WITH(NOLOCK)
          INNER JOIN Menu_CTE  AS B
   ON B.MenuID = A.ParentID
)

 

 

SELECT *
FROM Menu_CTE
Order BY SortOrder