How to get columns value separated by comma in MS-SQL?

There are many scenario come to show columns value separated by comma.

This can be achieved by FOR XML PATH with STUFF function.

Now you have tabled like below.

 

Now you want to fetch the records from table to show student name , corresponding subject which is separated by comma.

SQL-Query:-

 Select distinct s1.studentname,

 STUFF((select distinct ','+s2.[subject]

 from tbl_student s2 where s1.studentname=s2.studentname

 for xml Path(''),Type).value('.','varchar(max)'),1,1,'')subject

 from tbl_student s1

OutPut:-