How to Display Manager Name in a Table...?

Problem

I have the following Scenario where I need to display the manager name of the employee. I have only one table.

 

Solution:

CREATE TABLE test (

   EmpID INT,

   EmpName VARCHAR(100),

   MgrID INT)

 

INSERT INTO test VALUES (100, 'Rishi', NULL),

                        (101, 'Tapas', 100),

                        (102, 'Buddha', 100),

                        (103, 'Valluvar', 102)

 

T-SQL Script

SELECT e1.EmpID "Employee No",

 e1.EmpName "Employee Name",

 e2.EmpID "Manager Id",

 e2.EmpName "Managers Name"

 FROM test e1 RIGHT OUTER JOIN test e2

 ON e1.mgrId = e2.EmpID

 

Output: