라이브러리/시스템
계층 구조의 경로 표시 쿼리
눌프
2011. 7. 13. 16:23
WITH 구문을 이용해서 재귀적으로 구현
WITH tmpGroup (GroupID, GroupName, DisplayPath) AS
WITH tmpGroup (GroupID, GroupName, DisplayPath) AS
(
SELECT GroupID, GroupName, CONVERT(VARCHAR(512), '/' + GroupName)
FROM T_MachineGroup
WHERE ParentID is null
UNION ALL
SELECT n.GroupID, n.GroupName, CONVERT(VARCHAR(512), tmpGroup.DisplayPath + '/' + n.GroupName)
FROM T_MachineGroup n
JOIN tmpGroup ON n.ParentID = tmpGroup.GroupID
)
SELECT @DisplayPath = DisplayPath
FROM tmpGroup
WHERE GroupID = @IN_GroupID