Query to replace ID with Name within the same table?

Can anyone help me figure out this SQL Query?

I have the following table:

ID | Name | ManagerId
1 | Alice | 4
2 | Bob | 4
3 | Carol | 4
4 | Xavier | 5
5 | Zack | NULL

I want the new table to look like this:

ID | Name | Manager Name
1 | Alice | Xavier
2 | Bob | Xavier
3 | Carol | Xavier
4 | Xavier | Zack
5 | Zack | NULL

So essentially I want to replace the third column from “ManagerId” to “Manager Name,” where all information is retrieved in the same table.

Can anyone help me with the query?

I was thinking of something like the following, but it wasn’t even compiling:

WITH EmployeeCTE AS
(
   SELECT *, ID, Name, ManagerID OVER(PARTITION BY ID ORDER BY ID)
   FROM Employee
)
SELECT ID, Name, EmployeeCTE.Name as "Manager Name"
FROM EmployeesCTE

I had another attempt, but got stumped quickly:

SELECT ID, Name, ??
INNER JOIN Employee ON ID = Employee.ID
FROM Employee

Answer

Just use a join:

select e.*, m.name as manager_name
from employee e left join
     employee m
     on e.managerid = m.id

Advertisement

Answer