By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table TableA (name varchar(50), alias varchar(50), device_platform_id varchar(100));
create table TableB (name varchar(50), alias varchar(50));
insert into tableA select 'PFI-00602-10152','Brown Deer', 'PFD-00109;PFD-00117';
insert into tableB select 'PFD-00109','Black Ant';
insert into tableB select 'PFD-00117', 'Red Ant';
3 rows affected
select *
from tableA
cross apply (
select stuff((
select concat(';', b.alias)
from String_Split(device_platform_id,';')
join tableB b on b.name=value
for xml path ('')
),1,1, '') Device_Platform_Alias
)d
| name | alias | device_platform_id | Device_Platform_Alias |
|---|---|---|---|
| PFI-00602-10152 | Brown Deer | PFD-00109;PFD-00117 | Black Ant;Red Ant |