How to design an organization chart?

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

Manager	Employee	ManagerLevel	EmployeeLevel
Sánchez, Ken	Duffy, Terri	0	1
____Duffy, Terri	Tamburello, Roberto	1	2
________Tamburello, Roberto	Walters, Rob	2	3
________Tamburello, Roberto	Erickson, Gail	2	3
________Tamburello, Roberto	Goldberg, Jossef	2	3
________Tamburello, Roberto	Miller, Dylan	2	3
____________Miller, Dylan	Margheim, Diane	3	4
____________Miller, Dylan	Matthew, Gigi	3	4
____________Miller, Dylan	Raheem, Michael	3	4
________Tamburello, Roberto	Cracium, Ovidiu	2	3
____________Cracium, Ovidiu	D'Hers, Thierry	3	4
____________Cracium, Ovidiu	Galvin, Janice	3	4
________Tamburello, Roberto	Sullivan, Michael	2	3
________Tamburello, Roberto	Salavaria, Sharon	2	3
Sánchez, Ken	Bradley, David	0	1
____Bradley, David	Brown, Kevin	1	2
____Bradley, David	Wood, John	1	2
____Bradley, David	Dempsey, Mary	1	2
____Bradley, David	Benshoof, Wanida	1	2
____Bradley, David	Eminhizer, Terry	1	2
____Bradley, David	Harnpadoungsataya, Sariya	1	2
____Bradley, David	Gibson, Mary	1	2
____Bradley, David	Williams, Jill	1	2
Sánchez, Ken	Hamilton, James	0	1
____Hamilton, James	Krebs, Peter	1	2
________Krebs, Peter	Brown, Jo	2	3
____________Brown, Jo	Gilbert, Guy	3	4
____________Brown, Jo	McArthur, Mark	3	4
____________Brown, Jo	Simon, Britta	3	4
____________Brown, Jo	Shoop, Margie	3	4
____________Brown, Jo	Laszlo, Rebecca	3	4
____________Brown, Jo	Stahl, Annik	3	4
____________Brown, Jo	Mohan, Suchitra	3	4
____________Brown, Jo	Heidepriem, Brandon	3	4
____________Brown, Jo	Lugo, Jose	3	4
____________Brown, Jo	Okelberry, Chris	3	4
____________Brown, Jo	Abercrombie, Kim	3	4
____________Brown, Jo	Dudenhoefer, Ed	3	4
________Krebs, Peter	Dobney, JoLynn	2	3
____________Dobney, JoLynn	Baker, Bryan	3	4
____________Dobney, JoLynn	Kramer, James	3	4
____________Dobney, JoLynn	Anderson, Nancy	3	4
____________Dobney, JoLynn	Rapier, Simon	3	4
____________Dobney, JoLynn	Michaels, Thomas	3	4
____________Dobney, JoLynn	Kogan, Eugene	3	4
________Krebs, Peter	Hill, Andrew	2	3
____________Hill, Andrew	Ellerbrock, Ruth	3	4
____________Hill, Andrew	Johnson, Barry	3	4
____________Hill, Andrew	Higa, Sidney	3	4
____________Hill, Andrew	Ford, Jeffrey	3	4
____________Hill, Andrew	Hartwig, Doris	3	4
____________Hill, Andrew	Glimp, Diane	3	4
____________Hill, Andrew	Kearney, Bonnie	3	4
________Krebs, Peter	Maxwell, Taylor	2	3
____________Maxwell, Taylor	Smith, Denise	3	4
____________Maxwell, Taylor	Miller, Frank	3	4
____________Maxwell, Taylor	Keil, Kendall	3	4
____________Maxwell, Taylor	Hohman, Bob	3	4
____________Maxwell, Taylor	Male, Pete	3	4
____________Maxwell, Taylor	Tibbott, Diane	3	4
________Krebs, Peter	Campbell, John	2	3
____________Campbell, John	Dusza, Maciej	3	4
____________Campbell, John	Zwilling, Michael	3	4
____________Campbell, John	Reeves, Randy	3	4
____________Campbell, John	Khanna, Karan	3	4
____________Campbell, John	Adams, Jay	3	4
____________Campbell, John	Fitzgerald, Charles	3	4
____________Campbell, John	Masters, Steve	3	4
____________Campbell, John	Ortiz, David	3	4
________Krebs, Peter	Ray, Michael	2	3
____________Ray, Michael	Selikoff, Steven	3	4
____________Ray, Michael	Poland, Carole	3	4
____________Ray, Michael	Rettig, Bjorn	3	4
____________Ray, Michael	Osada, Michiko	3	4
____________Ray, Michael	Philips, Carol	3	4
____________Ray, Michael	Netz, Merav	3	4
________Krebs, Peter	D'sa, Reuben	2	3
____________D'sa, Reuben	Brown, Eric	3	4
____________D'sa, Reuben	Kaliyath, Sandeep	3	4
____________D'sa, Reuben	Frintu, Mihail	3	4
____________D'sa, Reuben	Creasey, Jack	3	4
____________D'sa, Reuben	Cook, Patrick	3	4
____________D'sa, Reuben	Martinez, Frank	3	4
____________D'sa, Reuben	Goldstein, Brian	3	4
____________D'sa, Reuben	Cornelsen, Ryan	3	4
________Krebs, Peter	Petculescu, Cristian	2	3
____________Petculescu, Cristian	Stadick, Betsy	3	4
____________Petculescu, Cristian	Wedge, Patrick	3	4
____________Petculescu, Cristian	Tiedt, Danielle	3	4
____________Petculescu, Cristian	Zimmerman, Kimberly	3	4
____________Petculescu, Cristian	Vande Velde, Tom	3	4
________Krebs, Peter	Loh, Kok-Ho	2	3
____________Loh, Kok-Ho	Hunter, Russell	3	4
____________Loh, Kok-Ho	Scardelis, Jim	3	4
____________Loh, Kok-Ho	Keyser, Elizabeth	3	4
____________Loh, Kok-Ho	Samant, Mandar	3	4
____________Loh, Kok-Ho	Tejani, Sameer	3	4
____________Loh, Kok-Ho	Yu, Nuan	3	4
____________Loh, Kok-Ho	Song, Lolan	3	4
____________Loh, Kok-Ho	Pournasseh, Houman	3	4
________Krebs, Peter	Mu, Zheng	2	3
____________Mu, Zheng	Ersan, Ebru	3	4
____________Mu, Zheng	Baker, Mary	3	4
____________Mu, Zheng	Homer, Kevin	3	4
____________Mu, Zheng	Kane, John	3	4
____________Mu, Zheng	Hill, Christopher	3	4
________Krebs, Peter	Liu, Jinghao	2	3
____________Liu, Jinghao	Ciccu, Alice	3	4
____________Liu, Jinghao	Cao, Jun	3	4
____________Liu, Jinghao	Fatima, Suroor	3	4
____________Liu, Jinghao	Evans, John	3	4
____________Liu, Jinghao	Moschell, Linda	3	4
____________Liu, Jinghao	Krapauskas, Mindaugas	3	4
____________Liu, Jinghao	Barbariol, Angela	3	4
____________Liu, Jinghao	Patten, Michael	3	4
____________Liu, Jinghao	Niswonger, Chad	3	4
____________Liu, Jinghao	Hall, Don	3	4
____________Liu, Jinghao	Entin, Michael	3	4
____________Liu, Jinghao	Lertpiriyasuwat, Kitti	3	4
________Krebs, Peter	Ackerman, Pilar	2	3
____________Ackerman, Pilar	Eaton, Susan	3	4
____________Ackerman, Pilar	Kuppa, Vamsi	3	4
____________Ackerman, Pilar	Ralls, Kim	3	4
____________Ackerman, Pilar	Berndt, Matthias	3	4
____________Ackerman, Pilar	Bischoff, Jimmy	3	4
________Krebs, Peter	Hamilton, David	2	3
____________Hamilton, David	Komosinski, Paul	3	4
____________Hamilton, David	Yukish, Gary	3	4
____________Hamilton, David	Caron, Rob	3	4
____________Hamilton, David	Cetinok, Baris	3	4
____________Hamilton, David	Holliday, Nicole	3	4
____________Hamilton, David	Rothkugel, Michael	3	4
________Krebs, Peter	Gubbels, Eric	2	3
____________Gubbels, Eric	Salmre, Ivo	3	4
____________Gubbels, Eric	Valdez, Sylvester	3	4
____________Gubbels, Eric	Sousa, Anibal	3	4
____________Gubbels, Eric	Smith, Samantha	3	4
____________Gubbels, Eric	Ting, Hung-Fu	3	4
____________Gubbels, Eric	Samarawickrama, Prasanna	3	4
____________Gubbels, Eric	Su, Min	3	4
____________Gubbels, Eric	Turner, Olinda	3	4
____________Gubbels, Eric	Sunkammurali, Krishna	3	4
____________Gubbels, Eric	Singh, Paul	3	4
________Krebs, Peter	Randall, Cynthia	2	3
____________Randall, Cynthia	Wang, Jian Shuo	3	4
____________Randall, Cynthia	Reátegui Alayo, Sandra	3	4
____________Randall, Cynthia	Watters, Jason	3	4
____________Randall, Cynthia	Ruth, Andy	3	4
____________Randall, Cynthia	Vanderhyde, Michael	3	4
____________Randall, Cynthia	Shabalin, Rostislav	3	4
________Krebs, Peter	Li, Yuhong	2	3
____________Li, Yuhong	Feng, Hanying	3	4
____________Li, Yuhong	Sam, Raymond	3	4
____________Li, Yuhong	Fakhouri, Fadi	3	4
____________Li, Yuhong	Sacksteder, Lane	3	4
____________Li, Yuhong	Randall, Linda	3	4
____________Li, Yuhong	Dyck, Shelley	3	4
____________Li, Yuhong	Earls, Terrence	3	4
________Krebs, Peter	Hay, Jeff	2	3
____________Hay, Jeff	Koenigsbauer, Kirk	3	4
____________Hay, Jeff	Steele, Laura	3	4
____________Hay, Jeff	Nayberg, Alex	3	4
____________Hay, Jeff	Cencini, Andrew	3	4
____________Hay, Jeff	Preston, Chris	3	4
________Krebs, Peter	Richins, Jack	2	3
____________Richins, Jack	Johnson, David	3	4
____________Richins, Jack	Young, Garrett	3	4
____________Richins, Jack	Metters, Susan	3	4
____________Richins, Jack	Li, George	3	4
____________Richins, Jack	Yalovsky, David	3	4
____________Richins, Jack	Ingle, Marc	3	4
____________Richins, Jack	Zabokritski, Eugene	3	4
____________Richins, Jack	Martin, Benjamin	3	4
____________Richins, Jack	Koch, Reed	3	4
____________Richins, Jack	Lawrence, David	3	4
____________Richins, Jack	King, Russell	3	4
____________Richins, Jack	Frum, John	3	4
____________Richins, Jack	Miksovsky, Jan	3	4
________Krebs, Peter	McAskill-White, Katie	2	3
____________McAskill-White, Katie	Hines, Michael	3	4
____________McAskill-White, Katie	Mirchandani, Nitin	3	4
____________McAskill-White, Katie	Decker, Barbara	3	4
____________McAskill-White, Katie	Chen, John	3	4
____________McAskill-White, Katie	Hesse, Stefen	3	4
________Krebs, Peter	Kim, Shane	2	3
____________Kim, Shane	McKay, Yvonne	3	4
____________Kim, Shane	Hite, Douglas	3	4
____________Kim, Shane	Esteves, Janeth	3	4
____________Kim, Shane	Rounthwaite, Robert	3	4
____________Kim, Shane	Penuchot, Lionel	3	4
________Krebs, Peter	Diaz, Brenda	2	3
____________Diaz, Brenda	McGuel, Alejandro	3	4
____________Diaz, Brenda	Northup, Fred	3	4
____________Diaz, Brenda	Liu, Kevin	3	4
____________Diaz, Brenda	Mohamed, Shammi	3	4
____________Diaz, Brenda	Patel, Rajesh	3	4
____________Diaz, Brenda	Nay, Lorraine	3	4
____________Diaz, Brenda	Nartker, Paula	3	4
____________Diaz, Brenda	Lee, Frank	3	4
____________Diaz, Brenda	Lloyd, Brian	3	4
____________Diaz, Brenda	Nusbaum, Tawana	3	4
____________Diaz, Brenda	Myer, Ken	3	4
____________Diaz, Brenda	Mares, Gabe	3	4
________Krebs, Peter	Kane, Lori	2	3
____________Kane, Lori	Munson, Stuart	3	4
____________Kane, Lori	Alderson, Greg	3	4
____________Kane, Lori	Gode, Scott	3	4
____________Kane, Lori	Flood, Kathie	3	4
____________Kane, Lori	Newman, Belinda	3	4
____Hamilton, James	Abolrous, Hazem	1	2
________Abolrous, Hazem	Wu, Peng	2	3
____________Wu, Peng	Charncherngkha, Sootha	3	4
____________Wu, Peng	Berglund, Andreas	3	4
____________Wu, Peng	Harrington, Mark	3	4
____________Wu, Peng	Alexander, Sean	3	4
________Abolrous, Hazem	Arifin, Zainal	2	3
____________Arifin, Zainal	Kharatishvili, Tengiz	3	4
____________Arifin, Zainal	Chai, Sean	3	4
____________Arifin, Zainal	Berge, Karen	3	4
____________Arifin, Zainal	Norred, Chris	3	4
____Hamilton, James	Wright, A. Scott	1	2
________Wright, A. Scott	Uddin, Sairaj	2	3
________Wright, A. Scott	Vong, William	2	3
________Wright, A. Scott	Brewer, Alan	2	3
________Wright, A. Scott	LaMee, Brian	2	3
____Hamilton, James	Altman, Gary	1	2
________Altman, Gary	Kleinerman, Christian	2	3
____________Kleinerman, Christian	Penor, Lori	3	4
____________Kleinerman, Christian	Macrae, Stuart	3	4
____________Kleinerman, Christian	Berry, Jo	3	4
____________Kleinerman, Christian	Coleman, Pat	3	4
________Altman, Gary	Hedlund, Magnus	2	3
Sánchez, Ken	Norman, Laura	0	1
____Norman, Laura	Barreto de Mattos, Paula	1	2
________Barreto de Mattos, Paula	Culbertson, Grant	2	3
________Barreto de Mattos, Paula	Chen, Hao	2	3
________Barreto de Mattos, Paula	Luthra, Vidur	2	3
________Barreto de Mattos, Paula	Martin, Mindy	2	3
________Barreto de Mattos, Paula	Johnson, Willis	2	3
____Norman, Laura	Liu, David	1	2
________Liu, David	Poe, Deborah	2	3
________Liu, David	Spoon, Candy	2	3
________Liu, David	Walton, Bryan	2	3
________Liu, David	Moreland, Barbara	2	3
________Liu, David	Tomic, Dragan	2	3
________Liu, David	Sheperdigian, Janet	2	3
________Liu, David	Seamans, Mike	2	3
____Norman, Laura	Kahn, Wendy	1	2
________Kahn, Wendy	Word, Sheela	2	3
____________Word, Sheela	Sandberg, Mikael	3	4
____________Word, Sheela	Rao, Arvind	3	4
____________Word, Sheela	Meisner, Linda	3	4
____________Word, Sheela	Ogisu, Fukiko	3	4
____________Word, Sheela	Hee, Gordon	3	4
____________Word, Sheela	Pellow, Frank	3	4
____________Word, Sheela	Kurjan, Eric	3	4
____________Word, Sheela	Hagens, Erin	3	4
____________Word, Sheela	Miller, Ben	3	4
____________Word, Sheela	Hill, Annette	3	4
____________Word, Sheela	Hillmann, Reinout	3	4
____Norman, Laura	Barber, David	1	2
Sánchez, Ken	Trenary, Jean	0	1
____Trenary, Jean	Conroy, Stephanie	1	2
________Conroy, Stephanie	Sharma, Ashvini	2	3
________Conroy, Stephanie	Connelly, Peter	2	3
____Trenary, Jean	Berg, Karen	1	2
____Trenary, Jean	Meyyappan, Ramesh	1	2
____Trenary, Jean	Bacon, Dan	1	2
____Trenary, Jean	Ajenstat, François	1	2
____Trenary, Jean	Wilson, Dan	1	2
____Trenary, Jean	Bueno, Janaina	1	2
Sánchez, Ken	Welcker, Brian	0	1
____Welcker, Brian	Jiang, Stephen	1	2
________Jiang, Stephen	Blythe, Michael	2	3
________Jiang, Stephen	Mitchell, Linda	2	3
________Jiang, Stephen	Carson, Jillian	2	3
________Jiang, Stephen	Vargas, Garrett	2	3
________Jiang, Stephen	Reiter, Tsvi	2	3
________Jiang, Stephen	Ansman-Wolfe, Pamela	2	3
________Jiang, Stephen	Ito, Shu	2	3
________Jiang, Stephen	Saraiva, José	2	3
________Jiang, Stephen	Campbell, David	2	3
________Jiang, Stephen	Mensa-Annan, Tete	2	3
____Welcker, Brian	Abbas, Syed	1	2
________Abbas, Syed	Tsoflias, Lynn	2	3
____Welcker, Brian	Alberts, Amy	1	2
________Alberts, Amy	Valdez, Rachel	2	3
________Alberts, Amy	Pak, Jae	2	3
________Alberts, Amy	Varkey Chudukatil, Ranjit	2	3