Concatenate Multiple Rows Within Single Row in SQL Server 2008

STUFF Function in SQL Server. In this article we will learn how to concatenate multiple rows within a 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  

Step 3

  1. Execute this SQL Query to get the student courseIds separated by a comma.
    1. USE StudentCourseDB  
    2. SELECT StudentID,  
    3. CourseIDs=STUFF  
    4. (  
    5.      (  
    6.        SELECT DISTINCT ', ' + CAST(CourseID AS VARCHAR(MAX))  
    7.        FROM StudentCourses t2   
    8.        WHERE t2.StudentID = t1.StudentID   
    9.        FOR XML PATH('')  
    10.      ),1,1,''  
    11. )  
    12. FROM StudentCourses t1  
    13. GROUP BY StudentID  
    The final result will display all the studentids and courseids separated by commas.

    STUFF Function in SQL Server
  2. Execute this query to get the student course names separated by a comma.
    1. USE StudentCourseDB  
    2. SELECT StudentID,  
    3. CourseNames=STUFF  
    4. (  
    5.     (  
    6.       SELECT DISTINCT ', 'CAST(g.CourseName AS VARCHAR(MAX))  
    7.       FROM Courses g,StudentCourses e   
    8.       WHERE g.CourseID=e.CourseID and e.StudentID=t1.StudentID   
    9.       FOR XMl PATH('')  
    10.     ),1,1,''  
    11. )  
    12. FROM StudentCourses t1  
    13. GROUP BY StudentID  
    The final result will be to display all the course names separated by a comma.

    STUFF Function in SQL Server