How to combine multiple rows from 3 tables into 1 string in SQL?

0
votes

I have 3 tables:
- Movie (MovieID, MovieName, etc.)
- Genre (GenreID, GenreName, etc.)
- MovieGenre (mapping table: MovieID & GenreID).

Basically, a movie can have multiple genres.
I've been struggling to find a query for a table that lists all movies and all of its genres. I need this to load data to a ASP.Net GridView via DataTable, SqlDataAdapter.

EX:
1   Guardians of the galaxy   Action, Adventure, Sci-Fi
2   Iron Man           Action, Adventure, Fantasy

I'm new to SQL and have searched for similar questions, found some answers, but it still didn't work.

2
sqlsql-server
Please show your own coding attempts, some sample input (i.e. some lines of create table .... and insert into ...) , the output you get and what is not satisfying about it. You might also want to take the tour. - Yunnosch

2 Answers

1
votes

This is a pain in SQL Server. One method is to put one genre per row, but you explicitly want all in one row.

select m.*,
       stuff( (select ', ' + g.genreName
               from moviegenre mg join
                    genres
                    on mg.genreid = g.genreid
               where mg.movieid = m.movieid
               for xml path ('')
              ), 1, 2, ''
            ) as genres
from movies m;

The genres are unlikely to have special characters. so explicitly using a type is probably not necessary.

0
votes

Here is one way to do it. Concatenate the genrename to comma separated values using for xml path('') trick 

SELECT M.moviename, 
       Stuff(CS.genres, 1, 1, '') 
FROM   movie M 
       CROSS apply(SELECT ',' + genrename 
                   FROM   moviegenre MG 
                          INNER JOIN genre G 
                                  ON MG.genreid = G.genreid 
                   WHERE  MG.movieid = M.movieid 
                   FOR xml path('')) CS (genres)