SQL Server return records with same record but split?

ok tricky and odd.
The project I am taking over well, the previous guys did a horrific job.

Basically, we have records of tracks and each track is suitable for a particular occasion.

there are multiple occasions.

What they did was for each track, they put in the "occasions" field, multiple occasions with a # dividing the occasions.

What I want to do is, if an occasion has say:

Wedding#Funeral

it means the track can be played at a Wedding or Funeral.
Since the records are doing an inner join in the SPROC, is there a way I can split that record so it brings back the same track but for the seperate occasions for both Wedding and Funeral?

Yes... but not easily... you'll need to get the field, then loop through it, looking for the next delimiter, then take everything up to that delimiter, put it in a temp table, then remove the text you jsut stored.... and repeat until you run out of delimiters.... It's a mess... your best bet would be to have one table for the tracks... one table for the occasions, then another table to bring them together and in the darkness bind them!... Oops... I mean, have a table that links the two:

Code:

---

tblTracks
  TrackId
  TrackName
.
.
.

---

Code:

---

tblOccasions
  OccasionID
  Occasion
.
.
.

---

Code:

---

tblTrackOccasion
  TrackId
  OccasionID
.
.
.

---

-tg

Thanks fellow MVP :)

Well I've managed to do this in C# without much fuss/hassle and implemented the IComparable on the object then did a sort before returning the collection back to the caller which then (sadly and badly) writes the entire page in javascript using Response.Write

may need to return to this unfortunately. quite odd and stupid how the previous team did it.

Now the client wants to sort things depending on ranking for each section but to get to the ranking have to do some other queries on other tables because of the way the table is set up *sigh*.

The way the results were being retrieved was with a simple SQL Statement. Now, if the field "stage" had a # in it, it meant it belongs to 2 stages so I (in C#) had to do a split on that, duplicate the record (in C#) and add a new stage, so the presentation would be duplicate records BUT for the different stages.

Example:


Stage Name
=============

Stage1

record1
record2
record3

Stage2

record2
record4
record5

as you can see, record2 is duplicated now as it belongs to both stage 1 and 2. The entry reading was "record1#record2" so I split it on the # and created another record in memory in C# and copied it on the fly


So now thats sorted in C#, they now want to be able to sort each record in the stage according to some rank (Which also exists somewhere) for each stage.... but since there are duplicate records created in C#, how would I do this?

any pointers in terms of logic? It's complicated, I know!