Get data from other table based on column with concatenated values

I have two tables:

category with columns:

id    name
1     business
2     sports
...

article with columns:

id   title    categories
1    abc      1|2|3
2    xyz      1|2

I know there should be a separate table for article categories but I was given this.

Is it possible to write a query that returns:

id    title     category_names
1     xyz       business,sports

I thought of splitting the string in article -> categories column, then use in query to extract name from category table but couldn’t figure it out.

Answer

You should fix your data model. But, you can do this in SQL Server:

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;

Here is a db<>fiddle.

Presumably, you already know the shortcomings of this data model:

• SQL Server has poor string handling functionality.
• Numbers should be stored as numbers not strings.
• Foreign key references should be properly declared.
• Such queries cannot make use of indexes and partitions.
• If you really want to store multiple values in a field, SQL Server offers both JSON and XML. Strings are not the right approach.