SQL Server 2017

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

select *
into article
from (values (1, 'abc', '1|2|3'), (2, 'xyz', '1|2')) v(id, title, categories)
2 rows affected

select *
into category
from (values (1, 'business'), (2, 'sports'), (3, 'other')) v(id, name)
3 rows affected

select a.*, s.names
from article a cross apply
     (select string_agg(c.name, ',') as names
      from string_split(a.categories, '|') ss join
           category c
           on try_convert(int, ss.value) = c.id
     ) s;


id
title
categories
names


1
abc
1|2|3
business,sports,other

2
xyz
1|2
business,sports