Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server - Add Row items as String with Comma Separated Values

Jul 16, 2013
Introduction:

In this article I will explain how to add row values as string with comma separated in
SQL server.
Description:
In previous articles I explained Exception handling in SQL Server, SQL Query to read xml data file, SQL Query to get latest unique records by date, difference between function and stored procedures, joins in SQL Server, substring function in SQL server and many articles relating to SQL server. Now I will explain how to add row values as string with comma separated in SQL Server.

Here I will explain with one example for that I have one table UserDetails like as shown below

Now I want bind the username row values into single row with comma separated values like as shown below

If we want to concatenate one column row values into single row we need to write query like this


DECLARE @name NVARCHAR(MAX)
SELECT @name = COALESCE(@name + ',', '') + UserName FROM UserDetails
SELECT UserNames = @name
If you observe above query I used function COALESCE it returns the first non-null expression among its arguments.

OutPut

We can concatenate rows in another way also

Another way

If we want to concatenate rows of column we can write query like as shown below

DECLARE @name NVARCHAR(MAX)
SET @name=''
SELECT @name = @name +','+UserName  FROM UserDetails
SET @name = Substring(@name, 2, (len(@name)))
SELECT @name as UserNames
OutPut



If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

11 comments :

Anonymous said...

nice

Irtekaz Ahmed Khan said...

Thanks sir like your post

varaprasad said...

very nice...

Innovapath said...

http://www.msnetframework.com/#aspnet.php

Unknown said...

Hi Suresh, Can you please help me through all the SQL cocepts. I like your concepts. It is so understanding.
I want all the contents from beginning to the end. I am trying to search concepts since beginning for SQL but getting confused where the tutotial starts. Please do reach me on my mail id pradyumna.jena540@gmail.com. I have experience of 1 year now as a Associate QA Engineer. Now i want to groom my skills.

Anonymous said...

Excellent !!!!!

Unknown said...

what in case of integer values? If I'm getting 2,8 as result.How can I use the result in 'in' operator?

Anonymous said...

very nice ....

Anonymous said...

Low performance

Anonymous said...

Good Topic.

Anonymous said...

Thanks Surya>>>>>>>>

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.