Click here to Skip to main content
11,428,497 members (65,354 online)
Click here to Skip to main content

Return Comma Separated String using SQL

, 30 Jan 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
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!!!  

License

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

Share

About the Author

Yogesh Gulve
Software Developer (Senior)
India India
Sr. Software Engineer in INDIA. Over 4.5+ years of experience in professional website development.

Comments and Discussions

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

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

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

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