-1

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Ashutosh
  • 4,371
  • 10
  • 59
  • 105
  • 2
    Consider this useful reading: [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – GMB Oct 20 '19 at 16:31

1 Answers1

1

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.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786