How can I create a view which does this:
;WITH Tree (ID, [NAME], PARENT_ID, Depth, Sort) AS
(
SELECT ID, [NAME], PARENT_ID, 0 AS Depth, CONVERT(varchar(255), [Name]) AS Sort FROM Category
WHERE PARENT_ID = 0
UNION ALL
SELECT CT.ID, CT.[NAME], CT.PARENT_ID, Parent.Depth + 1 AS Depth,
CONVERT(varchar(255), Parent.Sort + ' | ' + CT.[NAME]) AS Sort
FROM Category CT
INNER JOIN Tree as Parent ON Parent.ID = CT.PARENT_ID)
-- HERE IS YOUR TREE, Depths gives you the level starting with 0 and Sort is the Name based path
SELECT ID, [NAME], PARENT_ID, Depth, Sort FROM TreeORDER BY Sort
It should be as simple as:CREATE VIEW YourViewNameAS WITH Tree (ID, [NAME], PARENT_ID, Depth, Sort) AS ( SELECT ID, [NAME], PARENT_ID, 0 AS Depth, CONVERT(varchar(255), [Name]) AS Sort FROM Category WHERE PARENT_ID = 0 UNION ALL SELECT CT.ID, CT.[NAME], CT.PARENT_ID, Parent.Depth + 1 AS Depth, CONVERT(varchar(255), Parent.Sort + ' | ' + CT.[NAME]) AS Sort FROM Category CT INNER JOIN Tree as Parent ON Parent.ID = CT.PARENT_ID ) -- HERE IS YOUR TREE, Depths gives you the level starting with 0 and Sort is the Name based path SELECT ID, [NAME], PARENT_ID, Depth, Sort FROM Tree GO
No comments:
Post a Comment