Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

Comma Delimited Row

OK this may NOT be the best way to do this, so I need some advice on this one!

I have a column in my database called Departments and I'm storing the departments that are affected by an an incident.  I'm ONLY storing the department ID comma demlimited so for example...

Incident 1:
1,2
Incident 2:
1,3,6

However when running reports I need to first get the Incident by date range and then pull all the incidents in that date range get the departments affected and replace the Department ID from the string and replace it with the name of the department.

For example
1 1,2 would be come...
1 1
1 2
Then I need to get the Department Name...
1 A
2 B
and then put it back togehter
1 A,B
for the final output.

Hope this makes sense...but if there is a better way please let me know!

The reason I selected this method for storing all the affected departments in one field seperated by commas was to save space and or having another table.  Seemed like a good idea at the time.

In short I need to take a comma demlimited field get the names and turn it back into a comma demilited field for each record returned in the date range.

Thanks,
Mark
0
smithmrk
Asked:
smithmrk
  • 3
  • 2
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
you're better off splitting this out into lookup tables so you can use joins.

Otherwise you're going to have to do this on a per row basis which will kill your execution time.
0
 
smithmrkAuthor Commented:
Yeah...That's what I thought.

Was just trying to save having any more tables in the DB...but that appears to be the way to go.

Thanks,
Mark
0
 
smithmrkAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for smithmrk's comment #37742913

for the following reason:

Was hoping I could save having anymore tables by consolidating values into one field in on table.  But doesn't appear to be the best way to do this when having to extract the data later!
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
Suggestion was taken.
0
 
smithmrkAuthor Commented:
Opps!

Picked the wrong comment!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now