I've Tried Answer 2 from tis Question SQL Server: How to get all child records given a parent id in a self referencing table
and it works but it gives a Table For all nodes but order from parents to child
I just need to make a select Statement from that result to get all childs order like
Parent
-> child
-> child
-> last child
If you mean that you only need to show the hierarchy for one particular child, whose ID is the parameter of the query, then most likely it is better to build up the list upwards, starting from the specified item and walking up the hierarchy until reaching the top-most item.
For a solution, I'm going to borrow the data structure from this answer. And this is my solution:
/* sample data */
DECLARE @Table TABLE(
ID int,
ParentID int,
Name varchar(20)
);
INSERT INTO @Table (ID, ParentID, Name) SELECT 1, NULL, 'A';
INSERT INTO @Table (ID, ParentID, Name) SELECT 2, 1, 'B-1';
INSERT INTO @Table (ID, ParentID, Name) SELECT 3, 1, 'B-2';
INSERT INTO @Table (ID, ParentID, Name) SELECT 4, 2, 'C-1';
INSERT INTO @Table (ID, ParentID, Name) SELECT 5, 2, 'C-2';
/* parameter declaration & initialisation */
DECLARE @ID int;
SELECT @ID = 5;
/* the query */
WITH hierarchy AS (
SELECT * FROM @Table WHERE ID = @ID
UNION ALL
SELECT t.*
FROM @Table t
INNER JOIN hierarchy h ON t.ID = h.Parent
)
SELECT * FROM hierarchy;
