TSQL in SQL 2005: Query

Question

I have 2 columns in my table, called TaskSet and SkillsSelected.

The sample data as follow:

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

As you can see it's using comma to separate the data. I want a query that will give me the record that is not from the TaskSet that is not exist in the SkillsSelected so in this case will return:

SK000003 
SK000003, SK000004

Answer 1

This is not the best way to store the data. Why is it all in one record? It should be more normalized.

Answer 2

I'd sent this specification to whoever designed the table..truly awful!

I'd do this using a CLR stored procedure, it's possible with a curser but the code would be awful.

Answer 3

The best way to deal with comma separated lists in SQL Server is to create a UDF that returns a table type. See this link for details. MS documentation claims that a CLR UDF is faster, but here's an actual comparison of the two options in use.

Once that is in place, you can use:

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

Reference:

• EXCEPT and INTERSECT

Answer 4

First, implement the CLR Split() function that 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 could do it using a table of numbers, which would work sufficiently well, and doesn't force you to turn on CLR.

[Edit] Edited to include t.* in the list. This should really be changed to use the PK of t, or some other identifying feature.

[Edit] Sorry - had them the wrong way around.

And now that the question has changed, let's address the concatenation too:

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;