Concatenate Multiple Rows Within Single Row in SQL Server 2008

We can concatenate multiple rows within a single row using the predefined function STUFF available in SQL Server

Create 2 tables as in the following.

    Create Table Courses 
    ( 
      CourseID int primary key, 
      CourseName nvarchar(20) 
    ) 

INSERT INTO Courses(CourseId,CourseName) VALUES (1,'C#') 
INSERT INTO Courses(CourseId,CourseName) VALUES (2,'ASP.Net') 
INSERT INTO Courses(CourseId,CourseName) VALUES (3,'MVC') 
INSERT INTO Courses(CourseId,CourseName) VALUES (4,'WCF') 
INSERT INTO Courses(CourseId,CourseName) VALUES (5,'Share Point') 
INSERT INTO Courses(CourseId,CourseName) VALUES (6,'WPF') 
INSERT INTO Courses(CourseId,CourseName) VALUES (7,'SQL Server') 
INSERT INTO Courses(CourseId,CourseName) VALUES (8,'JQuery')

    CREATE TABLE StudentCourses 
    ( 
    StudentID int, 
    CourseID int 
    ) 

INSERT INTO StudentCourses(StudentID, CourseID) VALUES (1,1) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (1,3) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (1,5) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (2,2) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (2,4) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (2,5) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (3,3) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (3,6) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (4,7) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (4,8) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (5,1) 
INSERT INTO StudentCourses(StudentID, CourseID) VALUES (5,2)  

Execute this SQL Query to get the student courseIds separated by a comma.

    SELECT StudentID, 
    CourseIDs=STUFF 
    ( 
         ( 
           SELECT DISTINCT ', ' + CAST(CourseID AS VARCHAR(MAX)) 
           FROM StudentCourses t2  
           WHERE t2.StudentID = t1.StudentID  
           FOR XML PATH('') 
         ),1,1,'' 
    ) 
    FROM StudentCourses t1 
    GROUP BY StudentID 

Execute this query to get the student course names separated by a comma.

    SELECT StudentID, 
    CourseNames=STUFF 
    ( 
        ( 
          SELECT DISTINCT ', '+ CAST(g.CourseName AS VARCHAR(MAX)) 
          FROM Courses g,StudentCourses e  
          WHERE g.CourseID=e.CourseID and e.StudentID=t1.StudentID  
          FOR XMl PATH('') 
        ),1,1,'' 
    ) 
    FROM StudentCourses t1 
    GROUP BY StudentID