Luis Cazares (11/24/2014)

---

This is a bit simpler than Jason's example. Check the way I post the sample data, you're supposed to post it this way to get better help.

CREATE TABLE employee (

EmpName varchar(50),

EmpID int,

BossID int);

INSERT INTO employee VALUES

('Paul', 1, 0),

('John', 2, 1),

('Mary', 3, 2),

('Mark', 4, 2),

('Mike', 5, 4),

('Peter', 6, 0),

('Rick', 7, 6);

WITH rCTE AS(

SELECT EmpName, EmpID, BossID

FROM employee

UNION ALL

SELECT r.EmpName, r.EmpID, e.BossID

FROM employee e

JOIN rCTE r ON e.EmpID = r.BossID

WHERE e.BossID > 0

)

SELECT r.EmpName, e.EmpName Boss

FROM rCTE r

LEFT

JOIN employee e ON r.BossID = e.EmpID

ORDER BY r.EmpID

GO

DROP TABLE employee

You should try the looping option, you might find better performance than the recursive cte. You never know.

EDIT: Here's an alternative that you can use to test for performance.

SELECT EmpName, EmpID, BossID

INTO #employee

FROM employee;

WHILE @@ROWCOUNT > 0

INSERT #employee

SELECT r.EmpName, r.EmpID, e.BossID

FROM employee e

JOIN #employee r ON e.EmpID = r.BossID

WHERE e.BossID > 0

AND NOT EXISTS( SELECT 1

FROM #employee x

WHERE x.EmpID = r.EmpID

AND x.BossID = e.BossID);

SELECT r.EmpName, e.EmpName Boss

FROM #employee r

LEFT

JOIN employee e ON r.BossID = e.EmpID

ORDER BY r.EmpID;