2

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 instead of getting the number 654;653;526 I get the NAME field instead..... See the photo. Might explain this better

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

nkuebelbeck
  • 273
  • 2
  • 5
  • 15
  • 2
    This is just atrocious... I am sure it violates quite a bunch of normalization rules. – Otávio Décio Jun 02 '11 at 21:04
  • In the case where there are 3 values "654;653;526", do you want all 3 associated names to be returned? – Rob Sobers Jun 02 '11 at 21:07
  • I had no involvement in creating. I'm left picking up pieces. – nkuebelbeck Jun 02 '11 at 21:09
  • Yes I would like all 3 names returned. Not all field have 3. some have none some have up to 4 – nkuebelbeck Jun 02 '11 at 21:09
  • It would be a lot better to correct the DATA column in table 1 so that there is only one value in each row, then you could use normal SQL from now on. – joelhardi Jun 02 '11 at 21:12
  • possible duplicate of [\[T-SQL\] How to get the corresponding comma-separated text for a string of comma separated codes?](http://stackoverflow.com/questions/5631899/t-sql-how-to-get-the-corresponding-comma-separated-text-for-a-string-of-comma-s) – Mikael Eriksson Jun 02 '11 at 21:18

4 Answers4

2

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.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • They are fake field names. I can't really 'redo' this one. – nkuebelbeck Jun 02 '11 at 21:11
  • There is no way to effectively query this data. There are hacks but they wil lkill performance. You can't afford not to redesign unless your company doesn't actually own the database design becasue it came from a third-party. – HLGEM Jun 02 '11 at 21:13
  • This just gets my goat, why do third party vendors do such a lousy job of database design? I have never seen one yet that was even close to being acceptable in terms of data structure and I've had to support a lot of different ones through the years. – HLGEM Jun 02 '11 at 21:23
  • Since this will only be occasional data dump, I gave up and just used sql to extract to seperate csv's (replaced ';' with ',' in the sql). Then create 2 sheets then did and INDEX and MATCH function in excel...... -1 – nkuebelbeck Jun 02 '11 at 21:41
0

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

Community
  • 1
  • 1
Akram Shahda
  • 14,655
  • 4
  • 45
  • 65
0

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.

Jacco
  • 3,251
  • 1
  • 19
  • 29
0

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.

Tim
  • 5,371
  • 3
  • 32
  • 41