Return Comma Separated String using SQL

Introduction

There are lots of articles on the internet regarding this, still I writing this in a simple and very basic language. 

Background 

Many times while programming  we need some comma separated strings to show on the labels in forms, parameters in reports etc. For e.g. Gopal, Ramesh, Vikram and so on. 

So, how can we achieve this?  

Common Approach

I asked this question to the developers in our company - "What will be your first approach to get/return the comma separated string? Or what logic will you write to get this task done?". (Most of them are freshers). The answers which I get from them is like use Arraylist, String Builders, Dataset, Datatable or Collections  then apply For loop on that and add the comma in coding.

Ohhh... Lot of coding and little time consuming right??

The same approach was mine, before I came across the following solution. I want to avoid this coding in my code behind and I was just thinking if I am able to get this thing done from query itself, this will reduce my time as well as it'll be more effective.  

Situation 

Imagine, if we can return this string within few seconds then that will be great. It's possible by writing some lines of query in SQL.

Using the Code 

There are two methods through which we can achieve this thing. 

1st Method

DECLARE @listStr VARCHAR(MAX) 

SET @listStr = '' -- If you do not set this as blank (' ') then it'll return NULL value. Try it yourself  

SELECT  @listStr = TableField + ', ' + @listStr FROM TableName 

SELECT @listStr

In this method if you observe then you'll see one extra comma is added at the end of the string. So this is not the perfect method to use, because again you have to do that extra coding to remove the last comma.

There is a function call COALESCE in sql server. By using this you can get the exact output which you want.

2nd Method

DECLARE @listStr VARCHAR(MAX)   

SELECT @listStr = COALESCE(@listStr + ', ' ,'') + TableField   FROM TableName   

SELECT @listStr

It's always a good idea to use "COALESCE" because it returns the first nonnull expression among its arguments.

What is COALESCE?

"COALESCE accepts one or more column names of the same data type. The COALESCE function checks the value of each column in the order in which they are listed and returns the first non missing value. If only one column is listed, the COALESCE function returns the value of that column. If all the values of all arguments are missing, the COALESCE function returns a missing value."

For more information about COALESCE please check the following links

http://msdn.microsoft.com/en-us/library/aa258244(v=sql.80).aspx 

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002206368.htm  

Nice Example of COALESCE

http://www.techrepublic.com/article/creative-uses-for-coalesce-in-sql-server/6183173 

If you desired to show the dates in this fashion then you have to convert the date to VARCHAR keeping the format of date as it is. Go to the following link to know how to do that.

Sql Date Formats

http://www.sql-server-helper.com/tips/date-formats.aspx 

Conclusion

I hope this will help you in your current requirement or in future. 

Very easy yet effective.

Try it at your end.

And of course, Thanks to Pinal Dave (Here I found the solution):

http://blog.sqlauthority.com/2008/06/04/sql-server-create-a-comma-delimited-list-using-select-clause-from-table-column/  

 Happy Programming!!!