[DATABASE/SQLSERVER] CTE 재귀 호출 사용하기

■ CTE 재귀 호출을 사용하는 방법을 보여준다.

 

▶ 예제 코드 (SQL)

WITH CTE (ParentID, ID, [Level], [Path])
AS
(
    SELECT
        A.ParentID                           AS ParentID,
        A.ID                                 AS ID      ,
        1                                    AS [Level] ,
        CONVERT(NVARCHAR(MAX), '/' + A.Name) AS [Path]
    FROM Menu AS A WITH(NOLOCK)
    WHERE A.ParentID = -1

    UNION ALL

    SELECT
        A.ParentID                                      AS ParentID,
        A.ID                                            AS ID      ,
        B.[Level] + 1                                   AS [Level] ,
        CONVERT(NVARCHAR(MAX), B.[Path] + '/' + A.Name) AS [Path]
    FROM Menu AS A WITH(NOLOCK)
    JOIN CTE  AS B ON B.ID = A.ParentID
)
SELECT * FROM CTE AS A ORDER BY A.[Path];