|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
Anyone looking for a way to modernize legacy data or easily migrate to a more cost-effective database without sacrificing functionality will benefit from this seminar. View the Intro to Advantage Database Server now! |
|
#1
|
|||
|
|||
|
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 |
|
#2
|
||||
|
||||
|
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 Puzzle of the Month solved by sizeablegrin, etienne141 and L7Sqr, superior C/C++ programmers of the month |
|
#3
|
|||
|
|||
|
Thank you ! Your solution workes Scorpion. however, I tried making a VIEW with it, but the effort failed because I cant use the technique of using Cursors in defining a VIEW. How do i pull out the rabbit out of the hat now? A VIEW is what I ideally need.
Please Help!Thanking you in advance, 24-7-12-365 DUDE |
|
#4
|
||||
|
||||
|
Make a stored procedure instead and make the SELECT as the last statement. It'll work just like a view and will automatically trash the temp table when the connection terminates.
-Dave |
|
#5
|
|||
|
|||
|
Thanx Dave!!But I already thought of that.However, I am glad that I thought of what you said as it would mean that i am learning.Finally, the DUDE has begun his journey!!
Yours Sincerely, 24-7-12-365 DUDE |
![]() |
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Query Help |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|