SQL Server 2017

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

CREATE TABLE a (
  name nvarchar(80),
  area nvarchar(80));

INSERT INTO a
  VALUES (N'小明','1,2,5,2,1')
  , (N'小華','2,3,4,5,4,3,2');
  
SELECT *
FROM a;


name
area


小明
1,2,5,2,1

小華
2,3,4,5,4,3,2


CREATE TABLE b (
  area int,
  areaname nvarchar(80));

INSERT INTO b
  VALUES ('1', N'台北')
  , ('2', N'桃園')
  , ('3', N'台中')
  , ('4', N'高雄')
  , ('5', N'屏東');
  
SELECT *
FROM b; 


area
areaname


1
台北

2
桃園

3
台中

4
高雄

5
屏東


SELECT DISTINCT name,
  STUFF((SELECT ',' + TX.areaname
  FROM (SELECT a.name,b.areaname
  FROM a
  CROSS APPLY STRING_SPLIT(a.area, ',') AS C
  LEFT JOIN b ON b.area = CAST(c.value AS int)) TX
  WHERE TX.name = TY.name
  FOR xml PATH ('')), 1, 1, '') AS areanameAll
FROM (SELECT a.name,b.areaname
FROM a
CROSS APPLY STRING_SPLIT(a.area, ',') AS C
LEFT JOIN b ON b.area = CAST(c.value AS int)) TY;


name
areanameAll


小明
台北,桃園,屏東,桃園,台北

小華
桃園,台中,高雄,屏東,高雄,台中,桃園

name	areanameAll
小明	台北,桃園,屏東,桃園,台北
小華	桃園,台中,高雄,屏東,高雄,台中,桃園