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.
| Author |
Topic |
|
j2dna
Starting Member
11 Posts |
 Posted - 2011-05-24 : 10:27:04
|
| I've got a MySQL db that already exists that I need to pull over into SQL 2005. One of the tables has a category_id that is comma delimited (e.g. ,1,2,3,4,) and I need to join it to another table that contains the category name.Here's an example of the table structures:TableAID category_id hostname1 ,1,2,4, Example12 ,3, Example2TableBID category1 FL2 OH3 TX4 NVI need to return a result set with the category name instead of number. Like this...ID category hostname1 FL,OH,NV Example12 TX Example2I've messed with this for hours and can't seem to find a way of joining these tables. Anyone have a solution for this scenario? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-24 : 10:34:39
|
| select *from TableA ajoin TableB bon a.category_id like '%,' + convert(varchar(20),b.ID) + ',%==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-05-24 : 12:05:16
|
| Nigel's solution works. But, if you are migrating data, do you have the opportunity to fix your data structures? Putting your data into at least first normal form is going to help a lot down the road. |
|
|
|
j2dna
Starting Member
11 Posts |
Posted - 2011-05-24 : 13:59:22
|
| Thx guys...I haven't had a chance to give it a try yet, but yes, I do have the ability fix the structure before putting it into a table. Since the table is pretty small, I was thinking of creating a row for each category. I know this increases table size, but the table only has 60+ rows currently and will grow at a very slow pace (< 5 rows a week). |
|
|
|
|
|
|
|
|
Trying to Join Table with comma delimited field