티스토리 뷰
-- 트리구조 만들기 위한 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
'Data Base > MSSQL' 카테고리의 다른 글
Table 복사본 만들기 (0) | 2021.09.07 |
---|---|
[MSSQL] 테이블 언어 설정 차이로 인한 데이터 정렬 충돌 (0) | 2013.01.22 |
Having절의 용도 (0) | 2012.07.24 |
MSSQL DB Lock 확인 및 Kill (0) | 2012.02.24 |
MSSQL Intellisense가 안될경우 (0) | 2012.02.10 |