I thought about applying the concept of a hierarchical CTE (aka recursive CTE, see BOL for details). Basically, I would have used the smaller ID per row as "manager" and the other number as "employee" (this would actually take care of (101,102) and (102,101) not to cause an infinite loop.

But after spending quite some time with it, I have to give up.

The sample data you provided do not show any consistent logical pattern to apply a hierarchy structure to.

Examples:

In your second set of sample "employee" 105 would have to report to "manager" 104 and both have to report to 103 at the same time.

As far as I can see, the only way to solve that puzzle would be using a *cough* cursor *cough**cough* since it would handle one row at a time, ignoring inconsistent data. (Un)fortunately, my SQL Server version is not capable of programming a cursor (or maybe I'm not. Can't tell.) 😉

But the much more recommended way would be to get normalized data. Those could be transformed using hierarchy tools like recursive CTEs.

Btw: I can't think of a business case using transaction IDs that are related in a many to many relationship including circular reference across several levels...