-- Data
DECLARE @foo TABLE (
	[name] VARCHAR(255)
	, [Id] INT
	, [managerId] INT
	, [status] VARCHAR(3)
	)

INSERT @foo ([name], [Id], [managerId], [status])
      SELECT 'TheBoss', 1, NULL, 'YES'
UNION SELECT 'MD', 2, 1, 'No'
UNION SELECT 'Line Manager', 3, 2, 'No'
UNION SELECT 'Spod', 4, 3, 'No'
UNION SELECT 'Sub Boss', 5, 1, 'YES'
UNION SELECT 'Spod 2', 6, 5, 'No'

-- Query

DECLARE @map TABLE (
	[empId] INT
	, [managerId] INT
	, [path] VARCHAR(8000)
	, [found] BIT
	)

DECLARE @rows INT
DECLARE @maxIts INT
DECLARE @its INT


SET @rows = 1
SET @maxIts = 1000
SET @its = 0


INSERT INTO @map ([empId], [managerId], [path], [found])
SELECT
	f.[Id]
	, CASE f.[status] WHEN 'YES' THEN f.[Id] ELSE f.[managerID] END
	, f.[name]
	, CASE f.[status] WHEN 'YES' THEN 1 ELSE 0 END
FROM
	@foo f

-- Show before
SELECT * FROM @map

WHILE ( @rows > 0 AND @its < @maxIts ) BEGIN

	UPDATE m SET
		[managerId] = CASE f.[status] WHEN 'YES' THEN f.[Id] ELSE f.[managerId] END
		, [path] = [path] + ' -> ' + f.[name]
		, [found] = CASE f.[status] WHEN 'YES' THEN 1 ELSE 0 END
	FROM
		@map m
		JOIN @foo f ON f.[ID] = m.[managerID]
	WHERE
		m.[found] = 0

	SET @rows = @@ROWCOUNT
	SET @its = @its + 1

END

-- Show map after while loop
SELECT * FROM @map

-- Results
SELECT
	f.[name] AS [Employee]
	, f2.[name] AS [Manager Who can Say YES]
	, m.[path] AS [Management Path]
FROM
	@foo f
	JOIN @map m ON m.[empId] = f.[ID]
	LEFT JOIN @foo f2 ON f2.[Id] = m.[managerId]