Lets say you have the following table called item with columns (itemnum, categories) and a table-valued function called split that splits the given data by comma to rows.
Example table: itemnum categories A catg1, catg2 B catg2 C catg1, catg3, catg4
Here is a query that will join in those results:
select
i.id_item, c.Data as catg
from
item i
cross apply
dbo.split(i.categories, ‘,’) c
Resulting query: itemnum catg A catg1 A catg2 B catg2 C catg1 C catg3 C catg4