how to Combine Multiple Record Values into One Column

Question

I have 5 tables ( movies – categories - movie-catecories -  directors-Movie_Directors)

 

movies

movie_Id         int       

title      varchar            (50)     

image               varchar(100)               

 

categories

cat_Id              int       

cat_name         varchar(50)

 

movie-catecories

MovieCatrgory_ID       int       

cat_Id                          int       

movie_Id                     int       

           

 

directors

director_Id       int

Name   varchar(50)     

 

Movie_Directors

 

MovieDirectors_ID      int       

movie_Id                     int       

director_Id                   int       

DirectorName              varchar(50)     

                                   

 

 

movies

 

movie_Id	title	image
1	Hyde Park on Hudson	movies/1.jpg
2	Playing for Keeps	movies/2.jpg
3	Cheerful Weather for the Wedding	movies/3.jpg
4	Heleno	movies/4.jpg
8	Deadfall	movies/6.jpg

 

 

 

 

 

 

categories

 

Cat_Id	Cat-name
1	Drama
2	Romance
3	Comedy
4	Romance
5	Crime

 

movie-catecories

 

MovieCatrgory_ID	Cat_Id	movie_Id
1	1	1
2	1	2
3	2	3
4	2	4
5	3	8

 

 

Directors

 

Director_ID	Name
1	Roger Michell
2	Gabriele Muccino
3	Donald Rice
4	José Henrique Fonseca
5	Sung-Hee Jo

 

 

 

Movie_Directors

MovieDirectors_ID	movie_Id	director_Id	DirectorName
1	1	1	Roger Michell
2	1	2	Gabriele Muccino
4	1	3	Donald Rice
5	2	4	José Henrique Fonseca
6	2	5	Sung-Hee Jo

 

 

 

Sql:

SELECT DISTINCT movies.movie_Id, movies.title, movies.image, Movie_Directors.DirectorName, Movie_Directors.director_Id

FROM            movies INNER JOIN

                         Movie_Directors ON movies.movie_Id = Movie_Directors.movie_Id CROSS JOIN

                         directors

WHERE        (movies.title LIKE '%' + @title + '%')

 

Result

 

Movie_ID	tittle	image	DirectorName	director_Id
1	Hyde Park on Hudson	movies/1.jpg	Roger Michell	1
1	Hyde Park on Hudson	movies/1.jpg	Gabriele Muccino	2
1	Hyde Park on Hudson	movies/1.jpg	Donald Rice	3

 

 

I want to combine the DirectorName  in one record like this

 

Movie_ID	tittle	image	DirectorName	director_Id
1	Hyde Park on Hudson	movies/1.jpg	Roger Michell, Gabriele Muccino, Donald Rice	1-2-3

 

 

I have problem  in this code to combine the DirectorName  in one record

Please help me

SELECT DISTINCT movies.movie_Id, movies.title, movies.image,

substring(

                (select '; ' +Movie_Directors . DirectorName

                from Movie_Directors

                                inner join movies

                                on Movie_Directors.movie_Id = movies.movie_Id

               

                for xml path(")), 3, 1000) as CombineDirectorName

FROM            movies INNER JOIN

                         Movie_Directors ON movies.movie_Id = Movie_Directors.movie_Id CROSS JOIN

                         directors

WHERE        (movies.title LIKE '%' + @title + '%')

 

 

 

and display the result in  Repeater

 

 

<asp:Repeater ID="Repeater1" runat="server" >

    <HeaderTemplate> </HeaderTemplate>

    <ItemTemplate>

    <div style="width:100%;">

  

    <div class="excerpt">

     <a href="movie_details.aspx?id=<%# DataBinder.Eval(Container.DataItem, "movie_Id")%>" class="thumb" title="An image"><img src="<%# DataBinder.Eval(Container.DataItem, "image")%>" alt="Post" style="opacity: 1; float:left; width:80px ; height:100px; border:3px solid #fff ; margin:5px;"></a>

                     <a href="movie_details.aspx?id=<%# DataBinder.Eval(Container.DataItem, "movie_Id")%>" class="header"><h6><%# DataBinder.Eval(Container.DataItem, "title")%>

</h6></a>

                     <div style="padding:5px;"><%# DataBinder.Eval(Container.DataItem, " Movie_Directors . DirectorName ")%></div>

           

       </div>

    </div><br />

   

  

    <hr />

    </ItemTemplate>

    </asp:Repeater>

 

 

 

C#

 

protected void LinkButton1_Click(object sender, EventArgs e)

    {

       

           

            string cat_id = DropDownList1.SelectedValue;

            string keyword = TextBox1.Text;

            int? cid = int.Parse(cat_id);

           

            Repeater1.DataSource = dc.StoredProcedure1(TextBox1.Text, TextBox1.Text, cid);

            Repeater1.DataBind();

       

    }