Solved

Comma Delimited Row

Posted on 2012-03-20
5
240 Views
Last Modified: 2012-03-20
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
Comment
Question by:smithmrk
  • 3
  • 2
5 Comments
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 37742872
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
 

Author Comment

by:smithmrk
ID: 37742913
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
 

Author Comment

by:smithmrk
ID: 37742948
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
 
LVL 39

Expert Comment

by:Kyle Abrahams
ID: 37742949
Suggestion was taken.
0
 

Author Closing Comment

by:smithmrk
ID: 37742964
Opps!

Picked the wrong comment!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now