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!!!
