DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
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

Related article:

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

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE