Return Comma Separated Value From a Table

In this blog I will show you how to return a comma separated value from a table.

Introduction

We will create a table variable and populate it with some data. We will retrieve the studentname column values as a comma-separated string. Let us see how to do this.

Write the following script in SQL SERVER

DECLARE @Student TABLE  
(  
StudentIdintNOTNULL,  
StudentNamevarchar(20),  
Marks int  
)  
  
  
InsertInto @Student(StudentID,StudentName,Marks)Values (1,'Nitin Tyagi',200)  
InsertInto @Student(StudentID,StudentName,Marks)Values (2,'Amar Singh',400)  
InsertInto @Student(StudentID,StudentName,Marks)Values (3,'Vicky',300)  
  
SELECTSUBSTRING((  
SELECT','+CAST(StudentNameASVARCHAR)FROM @Student   
FORXMLPATH('')), 2,10000)ASStudentName 

Let us execute the query and check the output.

As we can see, we get the StudentName column value as a comma-separated string.