Wednesday, September 21, 2011

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]   

  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  

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
 -- Select query goes here

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

1 comment:

  1. Fantastic sharing i need it i have problem with it thanks.


Had to enable word verification due to number of spam comments received. Sorry for the inconvenience caused.