get the string after check 2nd comma separated string not in 1st comma separated string

Question

I have a table named BTSQMTAsk and it has two tasks in it. each task will have a column CompletedID, is a comma separated column. i need to return the completedID of a task not in the 2nd task(remember both are comma separated values). please help me to find this answer. i am stuck on this

TaskID       TaskName              CompletedID

1            Upload Signed SOW     1,2,3,5,4,9
2            Confirm Payment       1,5

in this, i must compare the completedID of both tasks and return result must be like '2,3,4,9'

- Output

CompletedID

2,3,4,9

So 1 and 5 are omitted because they exist in TaskID = 2 ? – ewahner Sep 30 '15 at 15:42 — ewahner, Sep 30 '15 at 15:42

score 0 · Answer 1 · answered Sep 30 '15 at 15:46

Try normalizing your table: split into two tables. It will make life much easier.

Table1 (TaskId, TaskName)
Table2 (TaskId, CompletedID)

Table2
TaskId  CompletedId
1       1
1       2
1       3
1       5
1       4
1       9
2       1
2       5

Then the query becomes much, much easier:

SELECT CompletedId FROM Table2
WHERE TaskId = 1
AND CompletedId NOT IN 
( SELECT CompletedId FROM Table2 WHERE TaskId = 2)

score 0 · Accepted Answer · edited May 23 '17 at 12:33

This solution will not require you to change your schema.

So use either of the 2 solutions to generate a function that will return a list from a delimited string: ToList() from Delimited String. Then do something like:

/* test data */
create table dbo.foo ( id integer, idlist varchar(100) )
insert into foo 
select 1, '1,2,3,4,5'
union
select 2, '1,5'
/* end test data */

declare @arr varchar(100)
set @arr = ''
select @arr = @arr + a.arrValue + ','
  from (
        select count(x.arrValue) as cnt, 
               x.arrValue
          from dbo.foo f
         cross apply dbo.fnArray(f.idlist, ',') x
         group by x.arrValue
         having count(x.arrValue) = 1
    ) a
set @arr = left(@arr, len(@arr) - 1)
print @arr

get the string after check 2nd comma separated string not in 1st comma separated string

2 Answers2