Execute the following script in Query Editor to demonstrate the listing of all the nodes of a subtree. AdventureWorks organization tree is used to list all employees who directly or indirectly report to a manager. Recursive CTE is applied with root starting at @ManagerID.

USE AdventureWorks;
GO
CREATE PROC DirectlyOrIndirectlyReportTo @ManagerID int
AS
BEGIN
WITH cteEmployeeTree AS
(
SELECT rootTree.EmployeeName, rootTree.ManagerName,
rootTree.EmployeeId, rootTree.ManagerId,
CONVERT(VARCHAR(MAX), rootTree.PathSequence) AS PathLabel
FROM
(
SELECT
EmployeeName = c.FirstName+' '+c.LastName,
ManagerName = cm.FirstName+' '+cm.LastName,
e.EmployeeId,
e.ManagerId,
char(64+ROW_NUMBER() OVER (ORDER BY e.EmployeeId)) AS PathSequence
FROM HumanResources.Employee e
JOIN Person.Contact c
ON e.ContactID = c.ContactID
LEFT JOIN HumanResources.Employee em
ON e.ManagerID = em.EmployeeID
JOIN Person.Contact cm
ON em.ContactID = cm.ContactID

WHERE e.ManagerId = @ManagerID
) rootTree

UNION ALL

SELECT subTree.EmployeeName, subTree.ManagerName,
subTree.EmployeeId,
subTree.ManagerId,
PathLabel=subTree.PathLabel + CONVERT(VARCHAR(MAX), subTree.PathSequence)
FROM
(
SELECT EmployeeName = c.FirstName+' '+c.LastName,
ManagerName = cm.FirstName+' '+cm.LastName,
e.EmployeeId, e.ManagerId,
cte.PathLabel,
PathSequence= char(64+ROW_NUMBER() OVER (ORDER BY e.EmployeeId))
FROM cteEmployeeTree cte
JOIN HumanResources.Employee e
ON e.ManagerId = cte.EmployeeId
JOIN Person.Contact c
ON e.ContactID = c.ContactID
JOIN HumanResources.Employee em
on em.EmployeeID = e.ManagerID
JOIN Person.Contact cm
ON em.ContactID = cm.ContactID

) subTree
)
SELECT EmployeeName, ManagerName, OrgChartPathLabel = PathLabel,
EmployeeID, ManagerID
FROM cteEmployeeTree
ORDER BY LEN (PathLabel), ManagerName, EmployeeName
END
GO

EXEC DirectlyOrIndirectlyReportTo 3
GO

-- Ken Sanchez, CEO, EmployeeID=109
EXEC DirectlyOrIndirectlyReportTo 109
GO