OK, now let's see how to generate FullPath:
Copy Code code as follows:
DECLARE @tbl TABLE
(
Id int
, ParentID int
)
INSERT into @tbl
(Id, ParentID)
VALUES (0, NULL)
, (8, 0)
, (12, 8)
, (16, 12)
, (17, 16)
, (18, 17)
, (19, 17)
with ABCD
As (
--Anchor
SELECT ID
, ParentID
, CAST (id as VARCHAR) as [Path]
From @tbl
WHERE ParentID is NULL
UNION All
--recursive Member
SELECT t.id
, T.parentid
, cast (A.[path] + ', ' + cast (t.id as VARCHAR) as VARCHAR) as [Path]
From @tbl as T
JOIN ABCD as a on t.parentid = a.ID
)
SELECT Id, ParentID, [Path]
from ABCD
WHERE Id not in (SELECT ParentID
From @tbl
WHERE ParentID is not NULL)
Return:
Id ParentID Path
----------- ----------- ----------------------
18 17 0,8,12,16,17,18
19 17 0,8,12,16,17,19
As simple as this, there is actually a hierarchytype in SQL Server 2008 that can solve this problem well. I'll write some post about Hierarchytype in the back.
I hope this post is helpful to you.
Author Peter Liu
