String separation column and join to another table

Question

Let's say I have 2 tables:

Job Offers:

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

Category:

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

We have a row split that takes a row, a separator and returns a table, my problem is that I'm really not sure how to integrate the table into my query in order to join job offers and categories.

I think it will 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]

I currently have two errors:

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

So the problem is how I build my subquery.

Answer 1

You can greatly simplify it 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

. select, . .

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

Answer 2

, :

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

SQL Server ( ).

: . , . ?

• .
• , .
• .