Show timings under same Movie Name

Question

   protected void Page_Load(object sender, EventArgs e)
      {
        // do connection Here.
        string strShowing = "Select m.MovieName, m.Restriction, m.Language,s.StartTime   from Movie m Inner Join Schedule s ON m.MovieID=s.MovieID";

         conLoginDetails.Close();

How to group the time in same movie details.

Answer 1

For string concatenation, AFAIK, there is no built in function do so. But you can use the FOR XML to do so. Something like this:

WITH CTE
AS
(
    Select m.MovieName, m.Restriction, m.Language, s.StartTime   
    from Movie m 
    Inner Join Schedule s ON m.MovieID=s.MovieID
    Where Status='Release'
)
SELECT 
  t1.MovieName,
  t1.Restriction,
  t1.Language,
  STUFF((
    SELECT ', ' + t2.StartTime
    FROM CTE t2
    WHERE t2.MovieName = t1.MovieName
    FOR XML PATH (''))
  ,1,2,'') AS StartTime
FROM CTE t1
GROUP BY t1.MovieName,
         t1.Restriction,
         t1.Language;

Use this query instead of the query you already using:

string strShowing = "--put the query here--";

Note that: I used a CTE instead of writing the original query one more time in the correlated subquery.