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