Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Comma Delimited Row

Posted on 2012-03-20
5
Medium Priority
?
255 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 40

Accepted Solution

by:
Kyle Abrahams earned 1500 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 40

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

719 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