Create a stored procedure,which takes a parameter agent_id, use cursors to iterate through each record, use the below query select Agent_Supervisor_Id from agent_details where agent_id=parameter, if above collected supervisor id is not equal to zero, iterate through the cursor,
select @SuperVisorID= Agent_Supervisor_Id from agent_details where agent_id=parameter
WHILE @@SuperVisorID>0
BEGIN
//set the above super visor id to a string, prepare a comma separated list of supervisors
select @SuperVisorID= Agent_Supervisor_Id from agent_details where agent_id=@@SuperVisorID
END
when you execute the above procedure, given 3 it should return 3,2
then execute some thing like select * from agent_details where agenit_id in (select @aboveProcName)