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