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
smithmrkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.