August 28th, 2003, 02:24 PM
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.
Up the Irons
What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
"Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
Down with Sharon Osbourne