The following T-SQL database scripts in Microsoft SQL Server Management Studio Query Editor demonstrate the building of an organizational chart for AdventureWorks & AdventureWorks2008 databases of AdventureWorks Cycles, the fictional bike company invented by Microsoft staff.

-- SQL recursive CTE to find subordinates of executive, manager & supervisor
-- Tree processing - find all descendants - find children - self-referencing table
DECLARE  @EmployeeID INT
SET @EmployeeID = 109
USE AdventureWorks;
WITH cteEmployeeName
     AS (SELECT FullName = FirstName + ' ' + LastName,
                EmployeeID
         FROM   HumanResources.Employee e
                INNER JOIN Person.Contact c
                  ON e.ContactID = c.ContactID),
     cteSubordinates
     AS (SELECT EmployeeID,
                LEVEL = 0
         FROM   HumanResources.Employee
         WHERE  EmployeeID = @EmployeeID
         UNION ALL
         SELECT e.EmployeeID,
                LEVEL + 1
         FROM   cteSubordinates cte
                INNER JOIN HumanResources.Employee e
                  ON cte.EmployeeID = e.ManagerID)
SELECT FullName,
       s.EmployeeID,
       LEVEL
FROM   cteSubordinates s
       INNER JOIN cteEmployeeName en
         ON s.EmployeeID = en.EmployeeID
/*
FullName          EmployeeID  Level
Ken Sánchez       109         0
David Bradley     6           1
Terri Duffy       12          1
Jean Trenary      42          1
Laura Norman      140         1
James Hamilton    148         1
Brian Welcker     273         1
Stephen Jiang     268         2
Amy Alberts       284         2

*/

------------
-- SQL Server 2008 tree processing functions - MSSQL organization chart - orgchart
-- T-SQL recursive Common Table Expression - CTE - SQL hierarchyid
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

-- Results

Related article:

http://www.sqlusa.com/bestpractices2005/organizationtree/