TSQL in SQL 2005: Query

Question

I have two columns in my table called TaskSetand SkillsSelected.

Sample data as follows:

TaskSet                        | SkillsSelected
--------------------------------------------------
SK000001, SK000004, SK000002   | SK000001, SK000002, SK000003
SK000002                       | SK000002, SK000003, SK000004

As you can see, using a comma to separate the data. I need a query that will give me an entry that does not belong to the TaskSet that does not exist in SkillsSelected, so in this case it will return:

SK000003 
SK000003, SK000004

Answer 1

First, we implement the CLR Split () function, which you get in the samples on the SQL 2005 installation media.

SELECT t.*, s.value
FROM yourTable t
CROSS APPLY
dbo.Split(SkillsSelected) s

EXCEPT

SELECT t.*, s.value
FROM yourTable t
CROSS APPLY
dbo.Split(TaskList) s
;

Alternatively, you can do this using a number table that will work well enough and not force you to enable the CLR.

[] , t. * . PK t .

[] - .

, , :

SELECT t.*, 
STUFF(
(
    SELECT ', ' + value
    FROM 
    (
    SELECT s.value
    FROM
    dbo.Split(SkillsSelected) s
    EXCEPT
    SELECT s.value
    FROM
    dbo.Split(TaskList) s
    ) v
    FOR XML PATH('')
),1,2,'') AS MissingSkills
FROM yourTable AS t;

Answer 2

SQL Server - UDF, . . . MS , CLR UDF , .

:

SELECT t.*
  FROM TABLE t
 WHERE EXISTS(SELECT value 
                FROM dbo.split(t.taskset)
              INTERSECT
              SELECT value 
                FROM dbo.split(t.skillsselected))

:

• EXCEPT INTERSECT

Answer 3

. ? .

Answer 4

, ... !

I would do this with the CLR stored procedure, this is possible with the cursor, but the code would be awful.