I miss read the requirement but seeing Lowells solution I saw the requirement, this works by making the results into a cross tab
CREATE TABLE [#Employee](
[EmpID] [int] NOT NULL,
[EmpName] [varchar](50) NOT NULL,
[SupID] [int] NULL)
GO
INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (1, 'ram', 2)
INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (2, 'sureh', 3)
INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (3, 'vimal', null)
DECLARE @Emp varchar(50) = 'ram'
;WITH CTE_Traverse_hierarchy
AS
(
--Anchor
SELECT EmpId RootEmpId ,EmpId,EmpName,SupId,0 Lvl
FROM #Employee
Where EmpName=@Emp
UNION ALL
SELECT
RootEmpId, E.EmpId,E.EmpName,E.SupId,Lvl+1 Lvl
FROM #Employee E
JOIN CTE_Traverse_hierarchy Parent on Parent.SupId=E.EmpId
Where
Lvl+1<=2
)
Select
MAX(CASE Lvl
WHEN 0 THEN EmpName
ELSE NULL
END) Supervisor
, MAX(CASE Lvl
WHEN 1 THEN EmpName
ELSE NULL
END) Manager
, MAX(CASE Lvl
WHEN 2 THEN EmpName
ELSE NULL
END) SubManager
from
CTE_Traverse_hierarchy
There are other improvements and it should work for any number of levels all you need to do is add Additional MAX clauses and Extent the range of the Where, possibly using a variable to define the number of Levels you want.
_________________________________________________________________________
SSC Guide to Posting and Best Practices