Recuring Routine to Build Hierarchy Levels

[#1]

I have a lookup table with two columns:  Resource and Manager

Resource     Manager

________     _______

Jones           Wilson

Smith           Wilson

Thomas        Andrews

Wilson          Thomas

Resource       Manager       Director       AVP

________       _______       _______      ____

Jones             Wilson         Thomas       Andrews

Smith             Wilson         Thomas       Andrews

I need to write a recursive routine to build out 5 level of management Hierarchy: Resource-Manager-Director-AVP-VP.

This is done by taking the original Resource and going back to the lookup table, assigning the Manager as the Resource - and taking the returned Manager value and making it the Director. - etc. etc...

Any Help?

[hic · Accepted solution]

The Hierarchy prefix does exactly this.

Hierarchy (ResourceID, ManagerID, Resource)

Load

          Resource as ResourceID,

          Resource,

          Manager as ManagerID

          From ...

HIC