CREATE TABLE #DestinationDept
(
	[DestinationDeptID] [int] IDENTITY(1,1) NOT NULL,
	[SNO] [int] NOT NULL,
	[Desc] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,	
	[ParentID] [int] NULL
) 

Insert into #DestinationDept (SNO,[Desc],ParentID)
	Select 1,'A',NULL Union all
	Select 2,'B',1 Union all
	Select 3,'E',2 Union all
	Select 4,'F',2 Union all
	Select 5,'C',1 Union all
	Select 6,'D',5 Union All
	Select 7,'H',4


--Select * from #DestinationDept

;with c as (Select DestinationDeptID,ParentID From #DestinationDept where ParentID = 2
			UNION ALL Select cs.DestinationDeptID,cs.ParentID From #DestinationDept as CS 
			Inner Join c on c.DestinationDeptID = cs.ParentID
			)
Select * from #DestinationDept As c2 
Where C2.DestinationDeptID in (Select c.DestinationDeptID from c)


drop table #DestinationDept

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

You can check my BLOG here

A
|
|=B 
| |=E
| \=F
| 
|=C
  \=D

.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

You can check my BLOG here

CREATE TABLE #Employees(Employee CHAR(1) NOT NULL PRIMARY KEY, ReportsTo CHAR(1))
INSERT INTO #Employees(Employee, ReportsTo)
SELECT 'A',NULL UNION ALL
SELECT 'B','A'  UNION ALL
SELECT 'C','A'  UNION ALL
SELECT 'D','C'  UNION ALL
SELECT 'E','B'  UNION ALL
SELECT 'F','B'


DECLARE @Start CHAR(1)
SET @Start='A';
 
WITH CTE AS
(SELECT 0 AS Depth, Employee, ReportsTo, CAST(Employee AS VARCHAR(MAX)) AS FullPath
FROM #Employees
WHERE Employee=@Start
UNION ALL
SELECT C.Depth+1, A.Employee,A.ReportsTo, C.FullPath  + '\' +  CAST(A.Employee AS VARCHAR(MAX))
FROM #Employees A 
INNER JOIN CTE C ON A.ReportsTo = C.Employee)
SELECT Employee,ReportsTo
FROM CTE
ORDER BY FullPath

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

You can check my BLOG here