Query to get the child level hierarchy data of a particular account.

Declare @TestTable Table
(
ID int,
Name varchar(50),
ParentID int
)

Insert into @TestTable values
(1,'A',null),
(2,'Aa',1),
(3,'Aaa',1),
(4,'Ab',2),
(5,'Abb',4),
(6,'B',null),
(7,'Ba',6),
(8,'Bbb',7),
(9,'Abc',4),
(10,'Acc',9)


Declare @ID int = 4

;with cte

(

ID , Name,ParentID,ParentName

)

As

(

 Select ID , Name,ParentID,
(select Name from @TestTable where ID = a.ParentID) parentName
 From @TestTable a where ID = @ID

Union All

Select m.ID , m.Name,c.ID,c.Name
from cte c inner join @TestTable m on m.ParentID = c.ID

)

Select * from cte Option (MAXRECURSION 32767)