Find matching values from one column to another column in same table sql-CodePudding

If I have this table:

I want to use tsql to go through each record and find the EmpID that correspnds to the SupervisorID for each record, and assign the LastName of the supervisor to SupervisorLastName. So the result would be

I started with this, but I think it is just checking if SupervisorID is equal to EmpID at the individual row level so it returns nothing.

case when (EmpID = SupervisorID) then CAST(LastName AS CHAR(30)) end AS SupervisorLastName

I guess my first example was misleading because I actually want to create the column SupervisorLastName. My example shows that it already exists. Thanks

CodePudding user response：

Could you try something like this?

SELECT t1.*, 
       t2.LastName AS SupervisorLastname 
FROM table1 t1 
LEFT JOIN (SELECT EmpID, LastName FROM table1) t2 ON t2.EmpID = t1.SupervisorID

CodePudding user response：

  ALTER TABLE TableName
  ADD SuperVisorLastName NVARCHAR(100) DEFAULT NULL;

  UPDATE TB1
  SET SuperVisorLastName = TB2.LastName
  FROM TableName TB1
  CROSS APPLY (SELECT TOP 1 * FROM TableName TB2 WHERE TB2.EmpID = 
  TB1.SupervisorID) TB2

I would do something like the above just add the column defaulted to null. Then do a cross apply to get the correct last name and update the column. Not copy and pastable but should give you a rough idea of how you could do it.

That being said you already technically know who the supervisor is because of the ID on there. So you do not need to unnecessarily repeat data in your data base.