Row Concatenation using For XML Path

--Declare the table variables
DECLARE @Course table(CourseId int Primary key identity(1,1),CourseName Varchar(50));
DECLARE @Students table(StudentId int Primary key identity(1,1),StudentName Varchar(50));
DECLARE @CourseAlloted table(CourseStudentAllotId int Primary key identity(1,1),CourseId int,StudentId int);

-- Insert Records
Insert into @Course values('C#'),('Asp.net'),('Sqlserver'),('MySql')
Insert into @Students values('John'),('David'),('Hendry'),('Smith'),('Watson')
Insert into @CourseAlloted values (1,1),(1,3),(2,1),(1,2),(3,4),(3,5)

--The query
;WITH studentCourseCTE AS(
SELECT 
        
        c.CourseName,
        s.StudentName,
        ca.CourseId,
        ca.StudentId
FROM @Course c 
JOIN @CourseAlloted ca on c.CourseId  = ca.CourseId
JOIN @Students s on s.StudentId = ca.StudentId)

,concateStudentCourseCTE AS(
SELECT 
        DISTINCT  
        c2.CourseName,  
        STUFF((	SELECT ','+ c1.StudentName 
				FROM studentCourseCTE c1 
				WHERE c1.CourseId=c2.CourseId 
				FOR XML PATH('') 
		),1,1, '') StudentName
FROM studentCourseCTE c2)

SELECT 
        Slno = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
        ,CourseName [Course Name]
        ,StudentName [Student Name]
FROM concateStudentCourseCTE

/*  Result 
--------------
Slno	Course Name	Student Name
1		Asp.net		John
2		C#		John,Hendry,David
3		Sqlserver	Smith,Watson