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 (
select value = y.i.value('(./text())[1]', 'varchar(max)')
from (
select x = convert(xml, '<i>' + replace(device_platform_id, ';', '</i><i>') + '</i>').query('.')
) as a cross apply x.nodes('i') as y(i)
)v
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 |