Oracle START WITH ~ CONNECT BY 절 MSSQL로 처리하기

* Oracle START WITH ~ CONNECT BY 절 MSSQL로 처리하기.

 

# Oracle

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

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

;