Convert Rows into comma separated using T-SQL

In this article I will explain how to convert table rows into comma separated value using T-SQL. To demonstrate this, we will create a student table and we will display student names into single column with comma separated.

Let's create a Student table first.

 CREATE TABLE Student
(
  RollNo int Identity,
  Name varchar(20)
 )

Insert some records into Student table

 INSERT INTO Student
(Name)
select 'Vinod'
UNION ALL
SELECT 'Ravi'
UNION ALL
SELECT 'Rahim'
UNION ALL
SELECT 'Gopa'

Select Student table, output will display like Figure 1-1.

Figure 1-1

Now we will write a SQL query which will convert Student name into single column with comma separated value.

Solution 1:

Declare @StudentNames varchar (max);
SELECT @StudentNames =ISNULL(@StudentNames +',','') + Name  FROM Student
SELECT @StudentNames as [NAME]

Solution 2:

Declare @StudentNames varchar (max);
SELECT  @StudentNames =COALESCE(@StudentNames +',','') + Name  FROM Student
SELECT  @StudentNames as [NAME]

Output of solution 1 and solution will look like Figure 1-2.

Figure 1-2