• 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