SQL lookup in SELECT statement

I've got and sql express database I need to extract some data from. I have three fields. ID,NAME,DATE. In the DATA column there is values like "654;654;526". Yes, semicolons includes. Now those number relate to another table(two - field ID and NAME). The numbers in the DATA column relate to the ID field in the 2nd table. How can I via sql do a replace or lookup so i开发者_C百科nstead of getting the number 654;653;526 I get the NAME field instead..... See the photo. Might explain this better

http://i.stack.imgur.com/g1OCj.jpg

Redesign the database unless this is a third party database you are supporting. This will never be a good design and should never have been built this way. This is one of those times you bite the bullet and fix it before things get worse which they will. Yeu need a related table to store the values in. One of the very first rules of database design is never store more than one piece of information in a field.

And hopefully those aren't your real field names, they are atriocious too. You need more descriptive field names.

Since it a third party database, you need to look up the split function or create your own. You will want to transform the data to a relational form in a temp table or table varaiable to use in the join later.

The following may help: How to use GROUP BY to concatenate strings in SQL Server?

This can be done, but it won't be nice. You should create a scalar valued function, that takes in the string with id's and returns a string with names.

This denormalized structure is similar to the way values were stored in the quasi-object-relational database known as PICK. Cool database, in many respects ahead of its time, though in other respects, a dinosaur.

If you want to return the multiple names as a delimited string, it's easy to do with a scalar function. If you want to return the multiple rows as a table, your engine has to support functions that return a type of TABLE.