Execute the following SQL Server T-SQL script in SSMS Query Editor to create a tree (organizational chart, orgchart) for AdventureWorks Cycles, a fictional mountain bike manufacturer.

Ken Sánchez is the CEO. Each path on the tree labelled with a unique label. The capital letter assignment to the immediate subordinates is done by the ROW_NUMBER function. The recursion is carried out by the UNION ALL set operator.

-- SQL organization tree - SQL organizational chart - SQL orgchart

-- SQL tree processing - SQL recursive CTE - Recursive Common Table Expression

USE AdventureWorks;

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 = convert(VARCHAR,''),

                        e.EmployeeId,

                        e.ManagerId,

                        char(64 + ROW_NUMBER()

                                    OVER(ORDER BY e.EmployeeId)) AS PathSequence

                 FROM   HumanResources.Employee e

                        INNER JOIN Person.Contact c

                          ON e.ContactID = c.ContactID

                 WHERE  e.ManagerId IS NULL) rootTree -- Anchor/root term

         UNION ALL

         SELECT subTree.EmployeeName, -- Recursive term

                subTree.ManagerName,

                subTree.EmployeeId,

                subTree.ManagerId,

                PathLabel = subTree.PathLabel + CONVERT(VARCHAR(MAX),

                            subTree.PathSequence)

         FROM   (SELECT EmployeeName = c.FirstName + ' ' + c.LastName,

                        ManagerName = convert(VARCHAR,cm.FirstName +

                         ' ' + cm.LastName),

                        e.EmployeeId,

                        e.ManagerId,

                        cte.PathLabel,

                        PathSequence = char(64 + ROW_NUMBER()

                                                   OVER(ORDER BY e.EmployeeId))

                 FROM   cteEmployeeTree cte

                        INNER JOIN HumanResources.Employee e

                          ON e.ManagerId = cte.EmployeeId

                        INNER JOIN Person.Contact c

                          ON e.ContactID = c.ContactID

                        INNER JOIN HumanResources.Employee em

                          ON em.EmployeeID = e.ManagerID

                        INNER JOIN Person.Contact cm

                          ON em.ContactID = cm.ContactID) subTree)

SELECT   EmployeeName,

         ManagerName,

         OrgChartPathLabel = PathLabel

FROM     cteEmployeeTree

ORDER BY OrgChartPathLabel

GO