Finding All level hierarchy order by passing parameter

Please refer the link : http://www.sqlservercentral.com/Forums/Topic1538141-3412-1.aspx

create table Hierarchy
(
ParentID int,
ChildID int
)

go

insert into Hierarchy values (101,1),(1,6),(101,2),(2,4),(2,5),(5,6),(5,7),(101,3),(3,5)

To understand this Please refer the below picture.




we are focusing on filtering the parent ID.

when we pass the parameter 101 , output should display as follows from the Hierarchy table.

101/1/6
101/2/4
101/2/5/6
101/2/5/7
101/3/5/6
101/3/5/7

For 1 sql server output as follows

1/6

For 2 sql server output as follows

2/4
2/5/6
2/5/7

For 3 sql server output as follows

3/5/6
3/5/7

For 5 SQL Server output as follows

5/6
5/7 

Solution :

Execute Hierarchy '101'

 

 

CREATE Procedure Hierarchy 

( 

@value int 

) 

AS begin 

 

--DECLARE @Value int =101; 

 

WITH rCTE AS( 

 SELECT CAST( CAST( ParentID AS varchar(10))+ '/' + CAST( ChildID  AS varchar(10))  

 AS varchar(8000)) AS String, 

  ParentID, 

  ChildID 

 FROM staging.Hierarchy 

 WHERE ParentID = @Value 

 UNION ALL 

 SELECT CAST( r.String + '/' + CAST( h.ChildID AS varchar(10)) AS varchar(8000)) 

  As String, 

  h.ParentID, 

  h.ChildID 

 FROM staging.Hierarchy h 

 JOIN rCTE r ON h.ParentID = r.ChildID 

) 

 

--select * from rCTE 

SELECT string 

FROM rCTE r 

WHERE NOT EXISTS( SELECT * 

    FROM rCTE x 

    WHERE x.String LIKE r.String + '%' --Contains the string 

    AND x.String > r.String) --And is larger than the string 

ORDER BY String 

End 

Output Results: