TSQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

TSQL Query

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2010-01-03 : 18:02:26

Hi there

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

The sample data as follow:
TaskSet | SkillsSelected;
SK000001, SK000004, SK000002 | SK000001, SK000002, SK000003

As you can see it's using comma to seperate the data. The query that we would like to pull is that

Give me the record that is not from the TaskSet that is not exist in the SkillsSelected so in this case will return: SK000003 for that row

I am thinking to use cursor BUt is there any alternative way?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-03 : 21:44:42

using a CSV parsing function like
- CSVTable
- fnParseList

with CROSS APPLY


select	t.TaskSet, t.SkillsSelected, ss.stringval
from	mytable t
	cross apply dbo.CSVTable(t.SkillsSelected) ss
where	not exists
	(
		select	*
		from	dbo.CSVTable(t.TaskSet) ts
		where	ts.stringval	= ss.stringval
	)

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-04 : 01:56:38

Also read about Normalization

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources