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
    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.


  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.

Up Next
    Ebook Download
    View all
    Learn
    View all