Click here to Skip to main content
13,088,701 members (56,465 online)
Click here to Skip to main content
Add your own
alternative version


6 bookmarked
Posted 30 Jan 2013

Return Comma Separated String using SQL

, 30 Jan 2013
Rate this:
Please Sign up or sign in to vote.
Return Comma Separated String using SQL


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


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.  


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


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


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.


"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  

Nice Example of COALESCE 

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 


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):  

 Happy Programming!!!  


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Yogesh Gulve
Software Developer (Senior)
India India
This member doesn't quite have enough reputation to be able to display their biography and homepage.

You may also be interested in...


Comments and Discussions

QuestionAnother approach Pin
code.happy22-Apr-14 0:57
membercode.happy22-Apr-14 0:57 
Questionuse this very easy solution Pin
gulam husain ansari15-Oct-13 5:56
membergulam husain ansari15-Oct-13 5:56 
GeneralMy vote of 2 Pin
Dmitry Barovik4-Feb-13 3:13
memberDmitry Barovik4-Feb-13 3:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.170813.1 | Last Updated 30 Jan 2013
Article Copyright 2013 by Yogesh Gulve
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid