query and comma delmited fields 1

[ecannizzo]

Hello. I have the following circumstance and I can't change the table design.

Table 1 columns
ID - identity field
Types - varchar field of comma delmited number (example: 1,2,3)

Table 2 columns
TypeID - identity field
Type - varchar (examples: NEWS or TRAVEL or WHATEVER)

Let's say NEWS is TypeID=1, TRAVEL is TypeID=2 and so on.

I need to select everything from table 1 but instead of returning 1,2,3 in the Types field I want to return NEWS,TRAVEL,WHATEVER

Can someone give me an example of how I can do this?

thanks!

 

[SQLDenis]

use the search on this site to search for the split function, you can probably also find it in George's archive

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[onpnt]

George's archive<--best resource in the sql forum

_{____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[ecannizzo]

I saw George's post on the split function and it requires sending in xml. My comma delimited data is directly from a database table so I will have to select it first. If I missed something in George's post, I'm sorry, but can someone lead me to an example?

 

[gmmastros]

Take a look at these:

faq183-3979
faq183-5207
faq183-6684

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ecannizzo]

The thing is I'm not passing the values into the stored proc. I have a query like this:

SELECT [RuleID]
,[ProductType]
,

      ,[Description]
  FROM [Code_Rules]

ProductType will return 1,2

However, there is a ProductType table. 1 and 2 are an id in this table. The id relates to a typename. So instead of returning 1,2 along with all the other fields in my initial select query, I would like to return NEWS,TRAVEL. So my results would look like:

Rule ID   ProductType     Code   Description
1         NEWS,TRAVEL     GG1    Testing

instead of
Rule ID   ProductType     Code   Description
1         1,2             GG1    Testing

I'm sure you've posted the answer in one of your FAQ so I'll continue looking. Ugh, I feel dumb! :)

 

[gmmastros]

This isn't exactly what you are looking for, but it's close. In the code I show, I create a couple table variables for demonstration purposes. You can copy/paste this code to Query Analyzer to see how it works.

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color]([COLOR=blue]Name[/color] [COLOR=blue]VarChar[/color](20), Colors [COLOR=blue]VarChar[/color](30))
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'George'[/color], [COLOR=red]'1,2'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'Denis'[/color], [COLOR=red]'2,4'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'KeyboardHands'[/color], [COLOR=red]'4'[/color])

[COLOR=blue]Declare[/color] @Colors [COLOR=blue]Table[/color](ColorId [COLOR=blue]Int[/color], Color [COLOR=blue]VarChar[/color](10))
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Colors [COLOR=blue]Values[/color](1,[COLOR=red]'Red'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Colors [COLOR=blue]Values[/color](2,[COLOR=red]'Blue'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Colors [COLOR=blue]Values[/color](3,[COLOR=red]'Green'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Colors [COLOR=blue]Values[/color](4,[COLOR=red]'Purple'[/color])

[COLOR=blue]Select[/color] [COLOR=blue]T[/color].Name, C.Color
[COLOR=blue]From[/color]   @Temp [COLOR=blue]T[/color]
       [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] @Colors C
         [COLOR=blue]On[/color] [COLOR=#FF00FF]CharIndex[/color]([COLOR=red]','[/color] + [COLOR=#FF00FF]Convert[/color]([COLOR=blue]varchar[/color](10), C.ColorId) + [COLOR=red]','[/color], [COLOR=red]','[/color] + [COLOR=blue]T[/color].Colors + [COLOR=red]','[/color]) > 0

Output:

[tt][blue]Name Color
-------------------- ----------
George Red
George Blue
Denis Blue
Denis Purple
KeyboardHands Purple[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

Hey man, I HATE the color purple

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ecannizzo]

Great and Thank You. This definitely gives me something to work with!