The following T-SQL database script in Query Editor demonstrates the building of an organizational chart for AdventureWorks2008 fictional bike company.

USE AdventureWorks2008;

GO

WITH cteDirectReports(ManagerID, EmployeeID, ManagerLevel)

 AS

(

SELECT OrganizationNode.GetAncestor(1),

OrganizationNode, OrganizationLevel-1

FROM HumanResources.Employee

WHERE OrganizationLevel=0

UNION ALL

SELECT e.OrganizationNode.GetAncestor(1),

e.OrganizationNode, OrganizationLevel-1

FROM HumanResources.Employee e

INNER JOIN cteDirectReports d

ON e.OrganizationNode.GetAncestor(1) = d.EmployeeID

)

SELECT Manager=

replicate('^', (ManagerLevel)* 4)+CO.LastName+', '+CO.FirstName,

Employee=C.LastName+', '+C.FirstName,

ManagerLevel, EmployeeLevel = ManagerLevel+1

FROM cteDirectReports DR

INNER JOIN HumanResources.Employee E

ON DR.EmployeeID = E.OrganizationNode

INNER JOIN Person.Person C

ON E.BusinessEntityID = C.BusinessEntityID

INNER JOIN HumanResources.Employee EM

ON DR.ManagerID = EM.OrganizationNode

INNER JOIN Person.Person CO

ON EM.BusinessEntityID = CO.BusinessEntityID

ORDER BY DR.EmployeeID

GO