String split column and join to another table

Question

Let's say I have 2 tables like this :

Job Offers:

+----+------------+------------+
| ID |    Name    | Categories |
+----+------------+------------+
|  1 | Programmer | 1,2        |
|  2 | Analyst    | 3          |
+----+------------+------------+

Categories:

+----+-----------------+
| ID |      Name       |
+----+-----------------+
|  1 | Programming     |
|  2 | Web Programming |
|  3 | Analysis        |
+----+-----------------+

We've got a string split that takes a string, a delimiter and returns a table, my problem is I'm really not sure how to integrate the table in my query to join the job offers and the categories.

My thinking is that it would be something like this :

SELECT
    O.[ID]                  AS OfferID,
    O.[Name]                AS OfferName,
    CAT.[CategoryName]      AS CategoryName,
    CAT.[CategoryID]        AS CategoryID
FROM
    JobOffers AS O
LEFT JOIN
(
    SELECT 
        O.[ID]              AS OfferID,
        C.[CategoryID]      AS CategoryID,
        C.[Name]            AS Name
    FROM
        (
            SELECT *
            FROM [dbo].[Split](O.[Categories], ',')
        ) AS CJ
    LEFT JOIN
        [Categories] AS C
        ON C.CategoryID = CJ.items  
) AS CAT ON CAT.OfferID = O.[ID]

Currently I have two errors saying:

• multi-part identifier O.[ID] cannot be bound
• multi-part identifier O.[Categories] cannot be bound
• incorrect syntax near AS (last line)

So clearly the problem is how I construct my subquery.

Answer 1

You can greatly simplify this to something like this.

SELECT
    O.[ID]                  AS OfferID,
    O.[Name]                AS OfferName,
    c.[CategoryName]      AS CategoryName,
    c.[CategoryID]        AS CategoryID
FROM
    JobOffers AS O
outer apply [dbo].[Split](O.[Categories], ',') s
left join Categories as C on c.CategoryID = s.Items

The concern I have is your splitter. If there is more than a single select statement the performance is going to suffer horribly. For a good explanation of various splitters available you can visit this article.

http://sqlperformance.com/2012/07/t-sql-queries/split-strings

Answer 2

From SQL SERVER 2016 we can use sql inbuilt function STRING_SPLIT as below :

SELECT * FROM JobOffers as j 
outer apply STRING_SPLIT(j.[Categories], ',') s
left join dbo.Categories as c on c.CategoryID =s.value

Answer 3

If you can live with one row per category then this will work:

select jo.*, c.name as category
from joboffers jo join
     categories c
     on ',' + jo.categories + ',' like '%,' + cast(c.id) + ',%';

Re-aggregating them into a string is painful in SQL Server (but very possible).

Note: you have a really, really bad data structure. So you should fix it as mentioned in a comment. Why is it bad?

• You are storing numbers as strings.
• You have ids that don't have a foreign key relationship to the reference table.
• You are storing multiple values in a single field.