How to create an organization tree?

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,




                CONVERT(VARCHAR(MAX),rootTree.PathSequence) AS PathLabel

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

                        ManagerName = convert(VARCHAR,''),



                        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




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


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

                        ManagerName = convert(VARCHAR,cm.FirstName +

                         ' ' + cm.LastName),




                        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,


         OrgChartPathLabel = PathLabel

FROM     cteEmployeeTree

ORDER BY OrgChartPathLabel



EmployeeName ManagerName OrgChartPathLabel
Ken Sánchez A
David Bradley Ken Sánchez AA
Kevin Brown David Bradley AAA
Sariya Harnpadoungsataya David Bradley AAB
Mary Gibson David Bradley AAC
Jill Williams David Bradley AAD
Terry Eminhizer David Bradley AAE
Wanida Benshoof David Bradley AAF
John Wood David Bradley AAG
Mary Dempsey David Bradley AAH
Terri Duffy Ken Sánchez AB
Roberto Tamburello Terri Duffy ABA
Rob Walters Roberto Tamburello ABAA
Gail Erickson Roberto Tamburello ABAB
Jossef Goldberg Roberto Tamburello ABAC
Dylan Miller Roberto Tamburello ABAD
Diane Margheim Dylan Miller ABADA
Gigi Matthew Dylan Miller ABADB
Michael Raheem Dylan Miller ABADC
Ovidiu Cracium Roberto Tamburello ABAE
Thierry D'Hers Ovidiu Cracium ABAEA
Janice Galvin Ovidiu Cracium ABAEB
Michael Sullivan Roberto Tamburello ABAF
Sharon Salavaria Roberto Tamburello ABAG
Jean Trenary Ken Sánchez AC
Janaina Bueno Jean Trenary ACA
Dan Bacon Jean Trenary ACB
François Ajenstat Jean Trenary ACC
Dan Wilson Jean Trenary ACD
Ramesh Meyyappan Jean Trenary ACE
Stephanie Conroy Jean Trenary ACF
Ashvini Sharma Stephanie Conroy ACFA
Peter Connelly Stephanie Conroy ACFB
Karen Berg Jean Trenary ACG
Laura Norman Ken Sánchez AD
Paula Barreto de Mattos Laura Norman ADA
Willis Johnson Paula Barreto de Mattos ADAA
Mindy Martin Paula Barreto de Mattos ADAB
Vidur Luthra Paula Barreto de Mattos ADAC
Hao Chen Paula Barreto de Mattos ADAD
Grant Culbertson Paula Barreto de Mattos ADAE
Wendy Kahn Laura Norman ADB
Sheela Word Wendy Kahn ADBA
Mikael Sandberg Sheela Word ADBAA
Arvind Rao Sheela Word ADBAB
Linda Meisner Sheela Word ADBAC
Fukiko Ogisu Sheela Word ADBAD
Gordon Hee Sheela Word ADBAE
Frank Pellow Sheela Word ADBAF
Eric Kurjan Sheela Word ADBAG
Erin Hagens Sheela Word ADBAH
Ben Miller Sheela Word ADBAI
Annette Hill Sheela Word ADBAJ
Reinout Hillmann Sheela Word ADBAK
David Barber Laura Norman ADC
David Liu Laura Norman ADD
Deborah Poe David Liu ADDA
Candy Spoon David Liu ADDB
Bryan Walton David Liu ADDC
Dragan Tomic David Liu ADDD
Barbara Moreland David Liu ADDE
Janet Sheperdigian David Liu ADDF
Mike Seamans David Liu ADDG
James Hamilton Ken Sánchez AE
Peter Krebs James Hamilton AEA
JoLynn Dobney Peter Krebs AEAA
Simon Rapier JoLynn Dobney AEAAA
James Kramer JoLynn Dobney AEAAB
Nancy Anderson JoLynn Dobney AEAAC
Bryan Baker JoLynn Dobney AEAAD
Eugene Kogan JoLynn Dobney AEAAE
Thomas Michaels JoLynn Dobney AEAAF
Taylor Maxwell Peter Krebs AEAB
Kendall Keil Taylor Maxwell AEABA
Bob Hohman Taylor Maxwell AEABB
Pete Male Taylor Maxwell AEABC
Diane Tibbott Taylor Maxwell AEABD
Denise Smith Taylor Maxwell AEABE
Frank Miller Taylor Maxwell AEABF
Jo Brown Peter Krebs AEAC
Guy Gilbert Jo Brown AEACA
Annik Stahl Jo Brown AEACB
Rebecca Laszlo Jo Brown AEACC
Margie Shoop Jo Brown AEACD
Mark McArthur Jo Brown AEACE
Britta Simon Jo Brown AEACF
Brandon Heidepriem Jo Brown AEACG
Jose Lugo Jo Brown AEACH
Suchitra Mohan Jo Brown AEACI
Chris Okelberry Jo Brown AEACJ
Kim Abercrombie Jo Brown AEACK
Ed Dudenhoefer Jo Brown AEACL
John Campbell Peter Krebs AEAD
David Ortiz John Campbell AEADA
Steve Masters John Campbell AEADB
Jay Adams John Campbell AEADC
Charles Fitzgerald John Campbell AEADD
Karan Khanna John Campbell AEADE
Maciej Dusza John Campbell AEADF
Michael Zwilling John Campbell AEADG
Randy Reeves John Campbell AEADH
Zheng Mu Peter Krebs AEAE
Ebru Ersan Zheng Mu AEAEA
Mary Baker Zheng Mu AEAEB
Kevin Homer Zheng Mu AEAEC
Christopher Hill Zheng Mu AEAED
John Kane Zheng Mu AEAEE
Jinghao Liu Peter Krebs AEAF
Alice Ciccu Jinghao Liu AEAFA
Jun Cao Jinghao Liu AEAFB
Suroor Fatima Jinghao Liu AEAFC
Linda Moschell Jinghao Liu AEAFD
John Evans Jinghao Liu AEAFE
Mindaugas Krapauskas Jinghao Liu AEAFF
Angela Barbariol Jinghao Liu AEAFG
Michael Patten Jinghao Liu AEAFH
Don Hall Jinghao Liu AEAFI
Chad Niswonger Jinghao Liu AEAFJ
Michael Entin Jinghao Liu AEAFK
Kitti Lertpiriyasuwat Jinghao Liu AEAFL
Reuben D'sa Peter Krebs AEAG
Ryan Cornelsen Reuben D'sa AEAGA
Brian Goldstein Reuben D'sa AEAGB
Mihail Frintu Reuben D'sa AEAGC
Sandeep Kaliyath Reuben D'sa AEAGD
Eric Brown Reuben D'sa AEAGE
Frank Martinez Reuben D'sa AEAGF
Patrick Cook Reuben D'sa AEAGG
Jack Creasey Reuben D'sa AEAGH
Cristian Petculescu Peter Krebs AEAH
Betsy Stadick Cristian Petculescu AEAHA
Kimberly Zimmerman Cristian Petculescu AEAHB
Patrick Wedge Cristian Petculescu AEAHC
Danielle Tiedt Cristian Petculescu AEAHD
Tom Vande Velde Cristian Petculescu AEAHE
Kok-Ho Loh Peter Krebs AEAI
Russell Hunter Kok-Ho Loh AEAIA
Jim Scardelis Kok-Ho Loh AEAIB
Nuan Yu Kok-Ho Loh AEAIC
Lolan Song Kok-Ho Loh AEAID
Houman Pournasseh Kok-Ho Loh AEAIE
Mandar Samant Kok-Ho Loh AEAIF
Sameer Tejani Kok-Ho Loh AEAIG
Elizabeth Keyser Kok-Ho Loh AEAIH
Pilar Ackerman Peter Krebs AEAJ
Susan Eaton Pilar Ackerman AEAJA
Vamsi Kuppa Pilar Ackerman AEAJB
Kim Ralls Pilar Ackerman AEAJC
Matthias Berndt Pilar Ackerman AEAJD
Jimmy Bischoff Pilar Ackerman AEAJE
David Hamilton Peter Krebs AEAK
Paul Komosinski David Hamilton AEAKA
Gary Yukish David Hamilton AEAKB
Michael Rothkugel David Hamilton AEAKC
Rob Caron David Hamilton AEAKD
Baris Cetinok David Hamilton AEAKE
Nicole Holliday David Hamilton AEAKF
Eric Gubbels Peter Krebs AEAL
Ivo Salmre Eric Gubbels AEALA
Paul Singh Eric Gubbels AEALB
Samantha Smith Eric Gubbels AEALC
Anibal Sousa Eric Gubbels AEALD
Sylvester Valdez Eric Gubbels AEALE
Hung-Fu Ting Eric Gubbels AEALF
Prasanna Samarawickrama Eric Gubbels AEALG
Min Su Eric Gubbels AEALH
Krishna Sunkammurali Eric Gubbels AEALI
Olinda Turner Eric Gubbels AEALJ
Jeff Hay Peter Krebs AEAM
Kirk Koenigsbauer Jeff Hay AEAMA
Laura Steele Jeff Hay AEAMB
Chris Preston Jeff Hay AEAMC
Alex Nayberg Jeff Hay AEAMD
Andrew Cencini Jeff Hay AEAME
Cynthia Randall Peter Krebs AEAN
Jian Shuo Wang Cynthia Randall AEANA
Sandra Reátegui Alayo Cynthia Randall AEANB
Jason Watters Cynthia Randall AEANC
Andy Ruth Cynthia Randall AEAND
Rostislav Shabalin Cynthia Randall AEANE
Michael Vanderhyde Cynthia Randall AEANF
Yuhong Li Peter Krebs AEAO
Hanying Feng Yuhong Li AEAOA
Raymond Sam Yuhong Li AEAOB
Fadi Fakhouri Yuhong Li AEAOC
Lane Sacksteder Yuhong Li AEAOD
Linda Randall Yuhong Li AEAOE
Terrence Earls Yuhong Li AEAOF
Shelley Dyck Yuhong Li AEAOG
Shane Kim Peter Krebs AEAP
Yvonne McKay Shane Kim AEAPA
Douglas Hite Shane Kim AEAPB
Janeth Esteves Shane Kim AEAPC
Robert Rounthwaite Shane Kim AEAPD
Lionel Penuchot Shane Kim AEAPE
Michael Ray Peter Krebs AEAQ
Steven Selikoff Michael Ray AEAQA
Carole Poland Michael Ray AEAQB
Bjorn Rettig Michael Ray AEAQC
Michiko Osada Michael Ray AEAQD
Carol Philips Michael Ray AEAQE
Merav Netz Michael Ray AEAQF
Katie McAskill-White Peter Krebs AEAR
Michael Hines Katie McAskill-White AEARA
Nitin Mirchandani Katie McAskill-White AEARB
Barbara Decker Katie McAskill-White AEARC
John Chen Katie McAskill-White AEARD
Stefen Hesse Katie McAskill-White AEARE
Jack Richins Peter Krebs AEAS
David Johnson Jack Richins AEASA
Garrett Young Jack Richins AEASB
Susan Metters Jack Richins AEASC
George Li Jack Richins AEASD
David Yalovsky Jack Richins AEASE
Marc Ingle Jack Richins AEASF
Eugene Zabokritski Jack Richins AEASG
Benjamin Martin Jack Richins AEASH
Reed Koch Jack Richins AEASI
David Lawrence Jack Richins AEASJ
Russell King Jack Richins AEASK
John Frum Jack Richins AEASL
Jan Miksovsky Jack Richins AEASM
Andrew Hill Peter Krebs AEAT
Ruth Ellerbrock Andrew Hill AEATA
Barry Johnson Andrew Hill AEATB
Sidney Higa Andrew Hill AEATC
Jeffrey Ford Andrew Hill AEATD
Doris Hartwig Andrew Hill AEATE
Diane Glimp Andrew Hill AEATF
Bonnie Kearney Andrew Hill AEATG
Lori Kane Peter Krebs AEAU
Stuart Munson Lori Kane AEAUA
Greg Alderson Lori Kane AEAUB
Scott Gode Lori Kane AEAUC
Kathie Flood Lori Kane AEAUD
Belinda Newman Lori Kane AEAUE
Brenda Diaz Peter Krebs AEAV
Alejandro McGuel Brenda Diaz AEAVA
Fred Northup Brenda Diaz AEAVB
Kevin Liu Brenda Diaz AEAVC
Shammi Mohamed Brenda Diaz AEAVD
Rajesh Patel Brenda Diaz AEAVE
Lorraine Nay Brenda Diaz AEAVF
Paula Nartker Brenda Diaz AEAVG
Frank Lee Brenda Diaz AEAVH
Brian Lloyd Brenda Diaz AEAVI
Tawana Nusbaum Brenda Diaz AEAVJ
Ken Myer Brenda Diaz AEAVK
Gabe Mares Brenda Diaz AEAVL
A. Scott Wright James Hamilton AEB
William Vong A. Scott Wright AEBA
Sairaj Uddin A. Scott Wright AEBB
Alan Brewer A. Scott Wright AEBC
Brian LaMee A. Scott Wright AEBD
Hazem Abolrous James Hamilton AEC
Peng Wu Hazem Abolrous AECA
Sean Alexander Peng Wu AECAA
Mark Harrington Peng Wu AECAB
Andreas Berglund Peng Wu AECAC
Sootha Charncherngkha Peng Wu AECAD
Zainal Arifin Hazem Abolrous AECB
Tengiz Kharatishvili Zainal Arifin AECBA
Sean Chai Zainal Arifin AECBB
Karen Berge Zainal Arifin AECBC
Chris Norred Zainal Arifin AECBD
Gary Altman James Hamilton AED
Christian Kleinerman Gary Altman AEDA
Pat Coleman Christian Kleinerman AEDAA
Lori Penor Christian Kleinerman AEDAB
Stuart Macrae Christian Kleinerman AEDAC
Jo Berry Christian Kleinerman AEDAD
Magnus Hedlund Gary Altman AEDB
Brian Welcker Ken Sánchez AF
Stephen Jiang Brian Welcker AFA
Michael Blythe Stephen Jiang AFAA
Linda Mitchell Stephen Jiang AFAB
Jillian Carson Stephen Jiang AFAC
Garrett Vargas Stephen Jiang AFAD
Tsvi Reiter Stephen Jiang AFAE
Pamela Ansman-Wolfe Stephen Jiang AFAF
Shu Ito Stephen Jiang AFAG
José Saraiva Stephen Jiang AFAH
David Campbell Stephen Jiang AFAI
Tete Mensa-Annan Stephen Jiang AFAJ
Amy Alberts Brian Welcker AFB
Jae Pak Amy Alberts AFBA
Ranjit Varkey Chudukatil Amy Alberts AFBB
Rachel Valdez Amy Alberts AFBC
Syed Abbas Brian Welcker AFC
Lynn Tsoflias Syed Abbas AFCA


Exam Prep 70-461