It doesn't really matter if it's a school assignment or whatever else it is, but it's always good courtesy to make an effort yourself before asking for solutions. We are in the teaching/learning-business here, not the doing-work-for-others business 
. And you're partly right that the CTE might not be the best solution for your exact requirement since you want the "recursiveness" in columns instead of rows, but for YOUR info a CTE is useful for a lot more than "storing values temporarily", which is not a very accurate way of describing a CTE btw (http://msdn.microsoft.com/en-us/library/ms190766.aspx). But if you have a fixed number of levels in your hierarchy a self join is a way to go:SELECT Emplid = emp.EmplID, EmployeeName = empname.Name_display, emp.Supervisor_ID, SupervisorName = supname.Name_display, ManagerID = man.EmplID, ManagerName = manname.Name_displayFROM job AS emp INNER JOIN name AS empname ON emp.EmplID = empname.EmplID LEFT OUTER JOIN name AS supname ON emp.Supervisor_ID = supname.EmplID LEFT OUTER JOIN job AS man ON emp.Supervisor_ID = man.EmplID LEFT OUTER JOIN name AS manname ON man.EmplID = manname.EmplID
I created this on the fly without any testing whatsoever but I think it will get what you need. I'm sure you can add the joins for the ApproverID and ApproverName yourself...- Lumbagohttp://xkcd.com/327/ 
Need help for getting reporting hierarchy
Posted - 2009-10-14 : 04:16:22
