Learn how to a build a cloud-first strategyRegister Now


How to convert multiline recordset to comma sperated string in sql server

Posted on 2009-04-28
Medium Priority
Last Modified: 2012-05-06
say I have a table  in Sql server 2005
name   value1  value2
name1  v11        v121
name1  v12        v122
name2   v21      v211
name2   v22      v222

how can I get a record set to  with one sql statement , no cursor , no procedure?

name   value1          value2
name1   v11,v12      v121,v122
name2   v21,v22      v211,v222
Question by:liqiug
LVL 31

Expert Comment

ID: 24257675
you have to create one function with coalesce which can return comma separated value and integrate it in your query.

to know the use of COALESCE, have a look at my article at

LVL 41

Accepted Solution

Sharath earned 2000 total points
ID: 24258391
try this
select distinct name,
       rtrim(substring(isnull((select ','+value1 from YourTable t1 where t1.name = t2.name for xml path('')),''),2,2000)),
       rtrim(substring(isnull((select ','+value2 from YourTable t1 where t1.name = t2.name for xml path('')),''),2,2000))
  from YourTable t2

Open in new window


Author Closing Comment

ID: 31575804
Thank you

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question