[SQL Server] Implement Oracle "START WITH ~ CONNECT BY" clause

[SQL Server]  Implement Oracle "START WITH ~ CONNECT BY" clause

# Oracle

1 2 3 4

SELECT LEVEL, CATE_ID, LTRIM (SYS_CONNECT_BY_PATH (CATE_NAME, ' > '), ' > ') || ' > ' AS CATE_NAME FROM TBL_CATEGORY START WITH CATE_ID = 1 CONNECT BY PRIOR CATE_ID = PARENT_CATE_ID

# SQL Server

1 2 3 4 5 6 7 8 9 10 11 12 13 14

WITH CTE_TABLE AS ( SELECT 1 AS LEVEL, A.CATE_ID, PARENT_CATE_ID, CATE_NAME, CONVERT(VARCHAR(100), CAST(A.CATE_NAME as VARCHAR(100)) + ' > ') AS CATE_NAME FROM dbo.TBL_CATEGORY A WHERE A.CATE_ID = 1 UNION ALL SELECT LEVEL + 1, M.CATE_ID, M.PARENT_CATE_ID, M.CATE_NAME, CONVERT(VARCHAR(100), N.CATE_NAME + ' > ' + CAST(M.CATE_NAME AS VARCHAR(100)) + ' > ' ) AS CATE_NAME FROM dbo.TBL_CATEGORY M JOIN CTE_TABLE N ON M.PARENT_CATE_ID = N.CATE_ID ) SELECT * FROM CTE_TABLE ;