CONCATENATE Column values into Rows

How do you CONCATENATE column values into rows?

Scenario: There is a table Student (ID (identity), StudentName, SubjectName). You need to concatenate all the subject of each student and display along with student name. Below is sample of input data:

ID	StudentName	SubjectName
1	Hari	Science
2	Tony	Biology
3	Hari	Maths
4	Ravi	Physics
5	Ravi	Chemistry
6	Ravi	Botany

 

Here is the expected output:

StudentName	SubjectName
Hari	Science,Maths
Ravi	Physics,Chemistry,Botany
Tony	Biology

 

Here is the solution: 

--Prepare data
 DECLARE @Student TABLE (
ID TINYINT IDENTITY(1,1)
,StudentName VARCHAR(30)
,SubjectName VARCHAR(30)
)
 INSERT @Student (StudentName, SubjectName) VALUES
 ('Hari','Science')
,('Tony','Biology')
,('Hari','Maths')
,('Ravi','Physics')
,('Ravi','Chemistry')
,('Ravi','Botany')

-- See input data
 SELECT * FROM @Student


----------------------------------------------------
-- T-SQL for string Concatenation using FOR XML PATH
----------------------------------------------------
DECLARE @Delimit VARCHAR(1) = ','

SELECT StudentName
      ,STUFF(
            (SELECT @Delimit + T1.SubjectName AS [text()]
            FROM @Student T1
            WHERE T1.StudentName = T2.StudentName
            FOR XML PATH(''))
      ,1 , 1, '') AS SubjectName
FROM @Student T2
GROUP BY StudentName
ORDER BY StudentName