I have been working around on a query given by one of my developers who works on crystal reports as a junior DBA, I always like to help them even though I am busy with other things as it will increase my knowledge in T-sql. The current scenario was simple as to do a self join. the schema for this scenario is given below
create table resource( res_id int,res_name varchar(20), man_id int)
populating the content and running the query below give the resource name and its manager
SELECT a.res_name,b.res_name AS manager FROM dbo.RESOURCE a INNER JOIN dbo.RESOURCE b ON b.res_id=a.man_id res_name manager ------------------ sam will sunny helen will micah
but next time my friend entered into my cubicle he need a different answer from the above he needs a result like this
sam will micah
so how will I chain the results I no idea where to start.