SQL Server Performance Forum – Threads Archive

Tree walking

Hi, Is there an easy way to perform tree walking in SQL server. I come from an Oracle background and was use to the ‘connect by’ and ‘starting with’ clauses in the select statement. Suddenly not having this has left me like a fish out of water. Thanks, Tim…

Can you describe "tree walking" in more detail? This term is not used in the SQL Server world, and I am not familiar with it. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

No problem. Tree walking is basically navigating along a hierarchy tree. For example: You could have a table that contains employees. One of the columns of this table could be the employee’s manager pointing back to a record in the same table. A simple walk would be to start with the CEO and list all their direct reports with each of their reports and so on putting the resulting data in order of the hierarchy. Or something like: CEO
VP SALES
DIR SALES NE
SALES PERSON
SALES PERSON
DIR SALES NW
SALES PERSON
SALES PERSON
SALES PERSON
DIR SALES S
SALES PERSON
SALES PERSON
VP FINANCE
DIR FINANCE
MGR A/R
A/R STAFF
A/R STAFF
MGR A/P
A/P STAFF Using this sort of technique you could list all employee’s below a given start point, or even above if you walk the tree the other way. Hope this helps, Tim…

Well you can accomplish that using a self Join but there is no direct method as far as I know. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

When you say you can do it with a self join how do you get the ordering right and prevent middle management from being included twice?

on sql server, this would be typically done in a stored procedure using a cursor. Self join is sometimes possible like gaurav says, but this is not often suitable because you need to know the maximum nest level (or longest branch) in order to use it.

Let’s have some sample data and work over it…. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

The BOL has some discussion of this under the topic: "Accessing and Changing Relational Data: Expanding Hierarchies" Always a good starting point. Nathan H.O.
Moderator
SQL-Server-Performance.com

Excuse my ingnorance but BOL? Thanks, Tim… P.S. I’ll get a couple of scripts to generate a table and test data as soon as I get a few mins.

Books online – installed with SQL Server Installation. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

I am posting the table as starting point. <pre>Drop Table CompanyPosts GO Create Table CompanyPosts ( Emp_id int Identity(1,1) NOT NULL PRIMARY KEY, Designation varchar(20) NOT NULL, ReportsTo int NULL Foreign Key References CompanyPosts(Emp_id) ) Insert into CompanyPosts (Designation, ReportsTo) Values(‘CEO’, NULL)–1 Insert into CompanyPosts (Designation, ReportsTo) Values(‘VP Sales’, 1)–2 Insert into CompanyPosts (Designation, ReportsTo) Values(‘VP FINANCE’, 1)–3 Insert into CompanyPosts (Designation, ReportsTo) Values(‘DIR SALES NE’, 2)–4 Insert into CompanyPosts (Designation, ReportsTo) Values(‘DIR SALES S’, 2)–5 Insert into CompanyPosts (Designation, ReportsTo) Values(‘DIR SALES NW’, 2)–6 Insert into CompanyPosts (Designation, ReportsTo) Values(‘DIR FINANCE’, 3)–7 Insert into CompanyPosts (Designation, ReportsTo) Values(‘MGR A/R’, 7)–8 Insert into CompanyPosts (Designation, ReportsTo) Values(‘MGR A/P’, 7)–9 Insert into CompanyPosts (Designation, ReportsTo) Values(‘SALES PERSON’, 4)– Insert into CompanyPosts (Designation, ReportsTo) Values(‘SALES PERSON’, 4)– Insert into CompanyPosts (Designation, ReportsTo) Values(‘SALES PERSON’, 5)– Insert into CompanyPosts (Designation, ReportsTo) Values(‘SALES PERSON’, 5)– Insert into CompanyPosts (Designation, ReportsTo) Values(‘SALES PERSON’, 6)– Insert into CompanyPosts (Designation, ReportsTo) Values(‘SALES PERSON’, 6)– Insert into CompanyPosts (Designation, ReportsTo) Values(‘SALES PERSON’, 6)– Insert into CompanyPosts (Designation, ReportsTo) Values(‘A/R STAFF’, <img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />– Insert into CompanyPosts (Designation, ReportsTo) Values(‘A/R STAFF’, <img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />– Insert into CompanyPosts (Designation, ReportsTo) Values(‘A/P STAFF’, 9)–</pre>Let’s see when I get the time for the rest (infact the most important [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />]) part of code. Gaurav Moderator Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

There is no equivalent to the Oracle CONNECT BY command in MS SQL Server. Read chapter 12 of "The Guru’s Guide to Transact-SQL" by Ken Henderson for some ideas on how to approach the hierarchy you need. Joe Celko’s "SQL for Smarties", chapter 26 also has suggestions for hierarchies. Finally, if you want lots of theory along with coding approaches, check out chapter 7 of Fabian Pascal’s "Practical Issues in Database Management".
— Greg Haselmann

]]>