계층구조 쿼리 (CTE)

SELECT * FROM MENUS;

WITH TREE_MENUS AS

(

SELECT 

PARENT_CD

, CODE

, NAME

, URL

, CONVERT(VARCHAR(255), CODE) AS SORT

, CONVERT(NVARCHAR(255), NAME) AS COL

, URL

FROM

MENUS

WHERE

PARENT_CD = '0'

UNION ALL

SELECT

A.PARENT_CD

, A.CODE

, A.NAME

, A.URL

, CONVERT(VARCHAR(255), B.CODE + ' > ' + A.CODE) AS SORT

, CONVERT(NVARCHAR(255), B.NAME + ' > ' + A.NAME) AS COL

, A.URL

FROM

MENUS A

INNER JOIN

TREE_MENUS B

ON A.PARENT_CD =  B.CODE

)

SELECT * FROM TREE_MENUS ORDER BY CODE

그외 비슷하지만 다른 방법

http://kokun.tistory.com/entry/MSSQL-WITH-%EC%A0%88%EA%B3%BC-CTE-2