계층구조 쿼리 (CTE)
MS-SQL2015. 10. 20. 23:04
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
'MS-SQL' 카테고리의 다른 글
ROW ↔ COLUMN (PIVOT) - 두번째 (0) | 2016.04.21 |
---|---|
ROW ↔ COLUMN (PIVOT) (0) | 2016.04.14 |
다수의 NULL 컬럼에 순차번호 넣기 (0) | 2015.07.01 |
MS-SQL 메모리 점유율 (0) | 2015.05.15 |
뽑아보자. 데이터를. (0) | 2014.12.31 |