SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE XP (
[XP001] varchar(4),
[XP002] varchar(4)); 

INSERT INTO XP VALUES('A001','A004');
INSERT INTO XP VALUES('A002','A003');

SELECT *
FROM XP;


XP001
XP002


A001
A004

A002
A003


CREATE TABLE ME (
[ME001] varchar(4),
[ME002] Nvarchar(10)); 

INSERT INTO ME VALUES('A001',N'業務部北區');
INSERT INTO ME VALUES('A002',N'業務部中區');
INSERT INTO ME VALUES('A003',N'業務部南區');
INSERT INTO ME VALUES('A004',N'業務部東區');

SELECT *
FROM ME;


ME001
ME002


A001
業務部北區

A002
業務部中區

A003
業務部南區

A004
業務部東區


SELECT XP.XP001 AS '修改前部門(XP001)',A.ME002 AS '修改前部門名稱',
XP.XP002 AS '修改後部門(XP002)',B.ME002 AS '修改後部門名稱'
FROM XP 
INNER JOIN ME AS A ON XP.XP001=A.ME001
INNER JOIN ME AS B ON XP.XP002=B.ME001
ORDER BY XP.XP001


修改前部門(XP001)
修改前部門名稱
修改後部門(XP002)
修改後部門名稱


A001
業務部北區
A004
業務部東區

A002
業務部中區
A003
業務部南區

ME001	ME002
A001	業務部北區
A002	業務部中區
A003	業務部南區
A004	業務部東區