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:
Incident 2:

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.

Who is Participating?
Kyle AbrahamsConnect With a Mentor Senior .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.
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.

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!
Kyle AbrahamsSenior .Net DeveloperCommented:
Suggestion was taken.
smithmrkAuthor Commented:

Picked the wrong comment!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.