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

Hi,
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
LVL 4
liqiugAsked:
Who is Participating?
 
SharathData EngineerCommented:
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

0
 
RiteshShahCommented:
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

http://www.sqlhub.com/2008/04/coalesce-function-in-sql-server-for.html
0
 
liqiugAuthor Commented:
Excellent!
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.