Recursion in SQL Server 2005

• neel.login

  Newbie

  Points: 3

  More actions

  July 5, 2008 at 10:18 am

  #209507

  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

• dbo.benyos

  SSCommitted

  Points: 1944

  More actions

  July 5, 2008 at 10:29 am

  #838516

  are you running SQL 2005?

  If so, you can use RECURSIVE CTE.

  The very same example is shown all over google, as well as MSDN.

  Look for it.

  Tal Ben Yosef
  www.TalBenYosef.com
  (visit my LinkedIn profile)