Stuck on Syntax - 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)

Stuck on Syntax

Author

Topic

Schnoz
Starting Member

4 Posts

Posted - 2010-03-10 : 09:09:10

I have been battling this problem for the past couple days and can not find an appropriate solution.

Let's say that I have a table with just three columns and it looks like this:

firstname, lastname, values

The values column holds a list of comma separated values where each value corresponds to a domain table. So a field would look like this:

"myFirstName", "mySecondName", "2,3,5,6"

The domain table would look something similar to this:

ID, Text
1, number1
2, number2
3, number3
4, number4
5, number5
6, number6

I want to create a select statement that pulls the information and instead of pulling the values separated by a comma, it pulls the text associated with those values. An example of the output would be this:

"myFirstName", "myLastName", "number2,number3,number5,number6"

Is this possible and how would I achieve this? The select statement I am trying to do this in is pretty big with lots of inner, left joins.

Thank you all for any help I can get.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-10 : 09:27:11

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

Schnoz
Starting Member

4 Posts

Posted - 2010-03-10 : 09:50:01

quote:
Originally posted by madhivanan

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

That's not quite was I am looking for.

I need to take 2,3,4 and convert that to number2,number3,number4 from a domain table. The method you gave me takes a second table and matches off of common IDs and doesn't even take into consideration the comma separated value list that is causing me the trouble.

Thanks for the reply, though.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-10 : 10:04:08

Try this

select t1.firstname, t1.lastname,replace(t1.values,t2.text) from table1 as t1
inner join table2 as t2 on ','+t1.value+',' like '%,'+cast(t2.id)+',%'

But read about Normalisation

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources