Recursion in SQL Server 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Recursion in SQL Server 2005

Author

Topic

neel
Starting Member

1 Post

Posted - 2008-07-05 : 05:20:45

I am struggling to display the results recursively.
My scenario is :
Table Name: Organization
Column Name: Managers
Column Name: Subordinates

The Organization table structure is like:
Managers Subordinates
Frank - Tim
Frank - Jacob
Frank - John
Frank - Mark
John - Randy
John - Dave
Dave - Tom
Dave - Jen
Mark - Joey
Mark - Steven

Now, when I will search for "Dave" then I should get the results for all the guys who report to him as well as all the managers whom "Dave" reports to. (Tree hive for Dave’s managers will be: Dave – John - Frank). So indirectly “Dave” reports to “Frank”.
So output for Dave will be (subordinates and his managers):
Tom
Jen
John
Frank

Can anybody please help me out?

Thanks,
Neel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-05 : 14:37:58

Have a good look in Books Online (the SQL Server help file/manual) about Recursive CTE (Common Table Expression).
Or do a search on this site. There are numerous examples.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-07 : 00:25:02

As an example:-

http://msdn.microsoft.com/en-us/library/ms186243.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources