How to join a table-valued function in T-SQL

Michael Roma

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