[MS-SQL] 재귀쿼리

오라클 SQL 문

select *
from TEST.MENU A
          START WITH A.UP_MENU_NO    = '1'
          CONNECT BY PRIOR A.MENU_NO = A.UP_MENU_NO 
          ORDER SIBLINGS BY A.SORT_NO

=========================================================================>

위의 오라클 sql 문을 ms-sql 문으로 변환

MS-SQL  문

;WITH CTE (UP_MENU_NO, MENU_NO,  MENU_LEVEL , MENU_NAME, SORT ) AS (

  SELECT 
       UP_MENU_NO AS UP_MENU_NO
       , '0' AS MENU_NO
       , MENU_LEVEL
       , MENU_NAME 
       , convert(varchar(255), right(N'00'+rtrim(SORT_NO),3)  ) AS SORT        //  1을 001 형태의 3자리 로 바꾸준다.
    FROM  MENU

    WHERE   UP_MENU_NO = '1' 
    UNION ALL 
    SELECT
         B.UP_MENU_NO AS UP_MENU_NO
         , B.MENU_NO AS MENU_NO
        , C.MENU_LEVEL + 1 AS MENU_LEVEL
        , B.MENU_NAME
        , convert(varchar(255), convert(nvarchar,C.SORT) + '|' +  right(N'00'+rtrim(B.SORT_NO),3)  ) AS SORT
    FROM    MENU B
    INNER JOIN CTE C ON B.UP_MENU_NO = C.MENU_NO
)
SELECT * FROM CTE order by SORT