Hello ppl,
Let me get straight to the point. I have three tables: -
1.)Movies-contains fields Movie_Id(PK) and Movie_Title
2.)Actors-contains fields Actor_Id(PK) and Actor_Name
3.)MoviesActors-contains fields Movie_Id(FK) and Actor_Id(FK)

PK->Primary Key
FK->Foreign Key

An Actor entity and a Movie entity share a many-to-many relationship between each other. Hence the need for the association table, MoviesActors.

Now I need to get a result set in the following format:-
Movie Id,Movie Title,Cast......where CAST is a comma-delimited string comprising the names of actors associated with the movie.

An example of the result set would be..

Movie Id Movie title Cast
---------- ---------------------- -----------------------------------
01 About A Boy Hugh Grant, Rachel Weisz

Hugh Grant and Rachel Weisz would be two separate entries in the Actors table.
Please Help!!

24-7-365 DUDE

Create a stored procedure that does the following:

1. Create a CURSOR to go through the rows of the Movies table.
2. Create a temp table (say #movie_cast)
3. For each row fetched by the cursor, insert the id and name into #movie_cast.
4. Next, populate an actors temp table containing the actors for the movie.
5. Select all the rows from the actors temp table and concatenate them. Update the corresponding row in #movie_cast with the concatenated string.
6. Skip back to step 3, if there are more rows to be fetched by the cursor.
7. Select all rows from #movie_cast (to return them),
8. Drop all temp tables.

Hope this helps. :D