Concatenate Multiple Rows Within Single Row in SQL Server 2008

STUFF Function in SQL Server

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
   
   1. Create Table Courses
   2. (
   3. CourseID int primary key,
   4. CourseName nvarchar(20)
   5. )
   Add some records to the Courses table, for example:
   
   1. INSERT INTO Courses(CourseId,CourseName) VALUES (1,'C#')
   2. INSERT INTO Courses(CourseId,CourseName) VALUES (2,'ASP.Net')
   3. INSERT INTO Courses(CourseId,CourseName) VALUES (3,'MVC')
   4. INSERT INTO Courses(CourseId,CourseName) VALUES (4,'WCF')
   5. INSERT INTO Courses(CourseId,CourseName) VALUES (5,'Share Point')
   6. INSERT INTO Courses(CourseId,CourseName) VALUES (6,'WPF')
   7. INSERT INTO Courses(CourseId,CourseName) VALUES (7,'SQL Server')
   8. INSERT INTO Courses(CourseId,CourseName) VALUES (8,'JQuery')
   9. SELECT * FROM Courses
   
   
   
2. StudentCourses
   
   1. CREATE TABLE StudentCourses
   2. (
   3. StudentID int,
   4. CourseID int
   5. )
   Add some records into the StudentCourses table, for example:
   
   1. INSERT INTO StudentCourses(StudentID, CourseID) VALUES (1,1)
   2. INSERT INTO StudentCourses(StudentID, CourseID) VALUES (1,3)
   3. INSERT INTO StudentCourses(StudentID, CourseID) VALUES (1,5)
   4. INSERT INTO StudentCourses(StudentID, CourseID) VALUES (2,2)
   5. INSERT INTO StudentCourses(StudentID, CourseID) VALUES (2,4)
   6. INSERT INTO StudentCourses(StudentID, CourseID) VALUES (2,5)
   7. INSERT INTO StudentCourses(StudentID, CourseID) VALUES (3,3)
   8. INSERT INTO StudentCourses(StudentID, CourseID) VALUES (3,6)
   9. INSERT INTO StudentCourses(StudentID, CourseID) VALUES (4,7)
   10. INSERT INTO StudentCourses(StudentID, CourseID) VALUES (4,8)
   11. INSERT INTO StudentCourses(StudentID, CourseID) VALUES (5,1)
   12. INSERT INTO StudentCourses(StudentID, CourseID) VALUES (5,2)
   13. SELECT * FROM StudentCourses