connecting a string based on id's and string

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

SQL Server Development (2000)

connecting a string based on id's and string

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-09-25 : 06:43:43

i have a colums which holds : 1,4,9
and another tables with the values for each id for example :
1=dog
2=cat
......
so when i select a field with 1,4,9 i want to recive : "cat,cow,bird"
how can i do this?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-25 : 07:16:07

You need to do simple join:

select t2.AnimalName
From Table1 T1
Join
Table2 T2
on T1.AnimalID = T2.AnimalID

Harsh Athalye
India.
"Nothing is Impossible"

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-09-25 : 07:17:55

but that will give me 3 lines
where i want to get the string in only 1 line!

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-25 : 08:42:24

why dont you normalize your data?

Chirag

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-09-25 : 09:04:44

i cant i have recived all the system as it is
its runing like this for years:(

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-25 : 09:22:50

If you can use the function..


Run the script from the following Link 
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685&SearchTerms=Numbers,table

GO 
--Function for getting the Description 
Create  Function GetDesc(@conc varchar(1000))
returns varchar(1000)
as 
Begin 
Declare @str varchar(1000)
Set @str = ''
Select @str = @str + Des + ',' From 
( 	Select  NullIf(SubString(',' + @conc + ',' , Number ,   
	CharIndex(',' , ',' + @conc + ',' , Number) - Number) , '') AS Ids  
	FROM 
	F_TABLE_NUMBER_RANGE(1,8000)
	Where 
	 Number <= Len(',' + @conc + ',') AND   
	SubString(',' + @conc+ ',' , Number - 1, 1) = ','   
	AND CharIndex(',' , ',' + @conc + ',' , Number) - Number > 0 
) as a 
Inner Join 
(
	Select 1 ID , 'Cat'Des Union All
	Select 4, 'Cow' Union All
	Select 9, 'Bird' 
) as b  on a.ids = b.id
Select @str = left (@str,len(@str)-1)
return @str 
End 
--- Other calculations.. 
GO
Declare @Table1 Table
(
  Ids varchar(100)
)

Insert @Table1
Select '1,4,9'




select dbo.GetDesc(ids) From @Table1 

output 
Cat,Cow,Bird

Hope this solves your issues..

Chirag

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-25 : 09:39:01

Read about Normalisation

http://www.datamodel.org/NormalizationRules.html
http://databases.about.com/od/specificproducts/a/normalization.htm

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources