SQL server - climb up in the tree structure
- by Vytas999
Hello. I have some sql table, named Object, which saves tree data in fields ObjectID, ParentID, and others. I have implemented recurse procedure, which select everything down by objectID from tree, like this:
1.
1.1.
1.2.
1.2.1.
...
Now o need to "Climb up" - by some ObjectID i need to select everything Up, like this:
1.2.1.
1.2.
1.
How i can do that?
In example, my "down" procedure looks like:
ALTER PROCEDURE [dbo].[Object_SelectDownByRoot_Simple]
@ObjectID int
AS
WITH tree (ObjectID, ParentID, ObjectName, ObjectCode) AS
(
SELECT ObjectID, ParentID, ObjectName, ObjectCode
FROM dbo.[ObjectQ] ofs
WHERE( ObjectID = @ObjectID )
UNION ALL
SELECT ofs.ObjectID, ofs.ParentID, ofs.ObjectName, ofs.ObjectCode
FROM dbo.[ObjectQ] ofs
JOIN tree ON tree.ObjectID = ofs.ParentID
)
SELECT
ObjectID, ParentID, ObjectName, ObjectCode
FROM tree
