Concatenete Multiple Rows Within Single Row in SQL Server 2008

Concatenate Multiple Rows within Single Row in Sql Server 2008

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

1. Create Database. Eg: StudentCourseDB (In this example)
   Create Database StudentCourseDB
2. Create 2 Tables(Courses, StudentCourses)
   Courses
   

   Create Table Courses

   (

     CourseID int primary key,

     CourseName nvarchar(20)

   )
3. Add some records in Courses Table
   
   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
   
   
4. Create another table StudentCourses
   

   CREATE TABLE StudentCourses

   (

   StudentID int,

   CourseID int

   )
5. Add some records into StudentCourses Table
   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
   
   
6. Execute the following SQL Query to get student courseIds separated by 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 studentid and courseids separated by comma
   
   
7. Execute this query to get student course names separated by 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 display all course names separated by comma