You've hit upon the "twist" part. I could be approaching this wrong. I'm assuming a recursion approach because to me this looks like a classic instance of the kind described on Microsoft's site where they describe Recursive Queries Using Common Table Expressions. This is org chart data.

https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

At the end of the day I don't really care how it get's done. I would like a solution which could be flexible to the number of levels of hierarchy.

Do you know of an alternate approach?