Quick suggestion

😎

USE TEEST;

GO

SET NOCOUNT ON;

IF OBJECT_ID(N'tempdb..#LookUp') IS NOT NULL DROP TABLE #LookUp;

CREATE TABLE #LookUp (Loc int, EmpID int)

INSERT INTO #LookUp

VALUES (1,123)

,(1,222)

,(2,222)

,(3,331)

,(3,332)

,(4,444)

,(4,441)

,(5,555)

,(6,552);

;WITH BASE_DATA AS

(

SELECT

LU.Loc

,ROW_NUMBER() OVER

(

PARTITION BY LU.Loc

ORDER BY @@VERSION

) AS RID

,LU.EmpID

FROM #LookUp LU

)

SELECT

BD.Loc

,MAX(CASE WHEN BD.RID = 1 THEN BD.EmpID END ) AS EMP1

,MAX(CASE WHEN BD.RID = 2 THEN BD.EmpID END ) AS EMP2

FROM BASE_DATA BD

GROUP BY BD.Loc

;

Output

Loc EMP1 EMP2

----------- ----------- -----------

1 123 222

2 222 NULL

3 331 332

4 444 441

5 555 NULL

6 552 NULL