Joining tables where data in a single column

[nsm1234567]

Hi There,

I'm wondering if someone can perhaps help me improve a script I'm writing.  My data is set out something like the below

High Level Code	Code	Description
9011	712	Agent
712	512	Agent_Household

The desired output is as follows:

Business Code	Attribute	Sub Attribute	Attribute Code	Sub Attribute Code
9011	Agent	Agent_Household	712	512

What I'm currently doing is loading the first table in twice.  When it loads a second time, I rename the fields so that e.g. High Level Code becomes "code" which allows me to join the two tables.  Is there not a better way to do this though?  Any help would be appreciated.

[simenkg]

You should look into the Hierarchy() function.

[petter]

Simens suggestion is good - an alternative solution could be to do an inner join (not let the associative model do an outer join) with the resident table - which should be very efficient compared to loading the table twice from the source.

Like this: