SQL Query Result into Comma Separated List

During my work I had encountered a situation where I need to get a comma separated list as an output from the values in a SQL query result. To elaborate it more, my query returns set of records (multiple records which match a certain criteria) as its result. Then there’s a column which I need to take as a comma separated value set.


For example, let’s imagine my query result is like this;

So, the output which I required is something like this;
  John, Kumar, Kalana, Chamath, Mark

The solution I have came up with is the below piece of code.

CREATE FUNCTION [dbo].[fnGetStudentList]   
(  
)  
RETURNS NVARCHAR (1000)  
AS  
BEGIN 

  DECLARE @StdList NVARCHAR (1000) 
 -- Variable to hold the comma separated list 

 SET @StdList = ''  

 SELECT @StdList = @StdList   
  + CASE WHEN (@StdList = '') THEN ''  ELSE ', '   END  
  + StdName  

 FROM (  
  SELECT StudentName AS StdName    
  FROM  MyTestDB.dbo.Students
 )
 
 RETURN  @StdList  
END 

Actually, in my real scenario, it had to output a data table. In that some columns need to contain comma separated values which depends on a value(s) contained in another column(s). Therefore I created this as an SQL function which can be called in a select query, and can be re-used.

Later, I have done a slight modification to the query by removing the CASE statement.  Modified code part is as below.

SELECT @StdList = COALESCE(@StdList+', ' ,' ') 
  + StdName
FROM (
 -- Select query goes here
)

COALESCE method in T-SQL returns the first non-null expression among its arguments.