comma seperated join mssql
select SM.ROLLNO,
SM.NAME,
SM.ADDRESS,
(
select ','+CM.CourseName
from dbo.CourseMaster as CM
where ','+SM.Course+',' like '%,'+CM.CourseId+',%'
for xml path(''), type
).value('substring(text()[1], 2)', 'varchar(max)') as Course
from dbo.StudentMaster as SM;