Here is something I put together using your sample table, that might help you get to where you want
Names Default To Here( 1 );
dt = Current Data Table();
maxLevels = Col Max( :EMPLEVEL );
dt << select where( :EMPLEVEL == 1 );
dtJoined = dt << subset( columns( :EMP_ID, :FULL_NAME ), selected rows( 1 ) );
For( m = 1, m <= N Cols( dtJoined ), m++,
Column( dtJoined, m ) << set name( Column( dtJoined, m ) << get name || "1" )
);
For( i = 2, i <= maxLevels, i++,
dt << select where( :EMPLEVEL == i );
dtTemp = dt << subset( columns( :EMP_ID, :FULL_NAME, :MGR_ID ), selected rows( 1 ) );
For( m = 1, m <= N Cols( dtTemp ), m++,
Column( dtTemp, m ) << set name( Column( dtTemp, m ) << get name || Char( i ) )
);
Eval(
Substitute(
Expr(
dtTemp2 = dtJoined << Join(
With( dtTemp ),
Merge Same Name Columns,
Match Flag( 0 ),
By Matching Columns( __emp__ = __mgr__ ),
Drop multiples( 0, 0 ),
Include Nonmatches( 0, 0 ),
Preserve main table order( 1 )
)
),
Expr( __emp__ ), parse(":EMP_ID" || Char( i - 1 )),
Expr( __mgr__ ), Parse(":MGR_ID" || Char( i ))
)
);
Close( dtTemp, nosave );
Close( dtJoined, nosave );
dtJoined = dtTemp2;
);
theNameList={};
for(i=1,i<=maxLevels,i++,
insert into(theNameList, parse(":FULL_NAME" || char(i)))
);
Tabulate(
Show Control Panel( 0 ),
Add Table(
Row Table( Grouping Columns( eval(theNameList) ) )
)
)
Jim