Select Query for Comma separated IDs

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 2000 Forums

Transact-SQL (2000)

Select Query for Comma separated IDs

Author

Topic

veeruu
Starting Member

10 Posts

Posted - 2006-02-09 : 00:01:07

hi,
my sample SQL Server DB Tables are like,
SID Skill
--- -------
1 JAVA
2 ORACLE
3 C
4 C++

PID Skillset
--- ---------
1 1,2,3
2 2,4
3 1,2,3,4
I need the Query to display Person skills as follows...
PID Skillset
--- --------------
1 Java,Oracle,C
2 Oracle,C++
3 Java,Oracle,C,C++

and another query for Search..
if i give the search string as Java or i will pass the SID 1. i need to diplay the person records which contains the SID.

output will be...
PID Skillset
--- --------------
1 Java,Oracle,C
3 Java,Oracle,C,C++

or

PID Skillset
--- ---------
1 1,2,3
3 1,2,3,4

Plz help meee..
Thanking you in advance for your help.

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-09 : 00:29:30

try this..
if input is SID
select * from #skillsets where ','+skillset+',' like '%,1,%'

or SKILL

SELECT PID, SKILLSET FROM #SKILLS A , #SKILLSETS B
where ','+skillset+',' like '%,'+ CAST(SID AS VARCHAR) +',%'
AND A.SKILL = 'JAVA'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-09 : 01:12:19

[code]create function dbo.CVS_code_to_string(@str varchar(1000))
returns varchar(1000)
as
begin
declare @strout varchar(1000)

select @strout = isnull(@strout, '') + s.Skill + ','
from CSVTable(@str) a inner join SID s
on a.numberval = s.SID
select @strout = left(@strout, len(@strout) - 1)
return @strout
end[/code]
Using CSVTable function from Dr Cross Join from here

Query 1 :
[code]select p.PID, dbo.CVS_code_to_string(p.Skillset) as [Skillset Name]
from PID p[/code]

Query 2 :
[code]declare @SkillID varchar(20)
select @SkillID = '1'

select *
from PID
where ',' + Skillset + ',' like '%,' + @SkillID + ',%'[/code]

Query 3 :
[code]declare @SkillStr varchar(20)
select @SkillStr = 'JAVA'

select p.PID, dbo.CVS_code_to_string(p.Skillset) as [Skillset Name]
from PID p, SID s
where s.Skill = @SkillStr
and ',' + Skillset + ',' like '%,' + convert(varchar(10), s.SID) + ',%'[/code]

----------------------------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-09 : 02:40:15

How about properly normalising the table?
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

veeruu
Starting Member

10 Posts

Posted - 2006-02-09 : 04:16:08

hi khtan..
Thanks alot for your Queries.you send exactly what my requirement needs.
and a bit more solutions is required. I need "Query 2" for more than one ID
i.e, if we give SkillID = 1,3 the result will be .. Person records which contains SkillID either 1 or 3 or Both.
Plz send the replay ..Im very Thankfull to you.

veeruu
Starting Member

10 Posts

Posted - 2006-02-09 : 04:54:31

Hi Khtan...
Some times "Actions speak louder than words". Now im speechless.I dont know how to express my hapiness in words. Finally i got the solution

declare @SkillID varchar(20)
select @SkillID = '7,10'

select distinct c.CandidateID,c.*, dbo.CVS_code_to_string(c.SecondarySkill) as [Skillset Name]
from dbo.CandidateMaster c inner join CSVTable(@SkillID) a
on ',' + SecondarySkill + ',' like '%,' + CONVERT(VARCHAR(50),a.numberval) + ',%'

this is my required query....
Thanks alot for your Help...
Veeruu..
veeruu@gamil.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-09 : 04:55:04

As Madhivanan pointed out. If you are able to proper normalize your table, you will not have all these problem

----------------------------------
'KH'
everything that has a beginning has an end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources