Returning All Children With A Recursive Select

Good day everyone! I've got a graph. First, I know how to build simple recursive selections. I read some info on msdn. In this image you can see that (for example) the top node o

Solution 1:

That sounds like a straightforward CTE. You can pass along the root of the influence in a separate column:

; with  Influence as
        (
        select  who_acts
        ,       on_whom_influence
        ,       who_acts as root
        from    dbo.YourTable
        union all
        select  child.who_acts
        ,       child.on_whom_influence
        ,       parent.root
        from    Influence parent
        join    dbo.YourTable child
        on      parent.on_whom_influence = child.who_acts
        )
select  root
,       on_whom_influence
from    Influence
orderby
        root
,       on_whom_influence

Example on SQL Fiddle.