Concatenate Multiple Rows Within Single Row in SQL Server 2008

Description

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

Step 1

Create a database.

For example: StudentCourseDB (in my example)

Step 2

Create 2 tables as in the following.

1. Courses
   

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

   Add some records to the Courses table, for example:
   

   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')
   SELECT * FROM Courses

   
   
   
2. StudentCourses
   

   CREATE TABLE StudentCourses
   (
   StudentID int,
   CourseID int
   )

   Add some records into the StudentCourses table, for example:
   

   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)
   SELECT * FROM StudentCourses

   
   

Step 3

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

   USE StudentCourseDB
   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

   The final result will display all the studentids and courseids separated by commas.
   
   
   
2. Execute this query to get the student course names separated by a comma.
   

   USE StudentCourseDB
   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

   The final result will be to display all the course names separated by a comma.