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

Posted on 2009-04-28
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

    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 40

    Accepted Solution

    try this
    select distinct name,
           rtrim(substring(isnull((select ','+value1 from YourTable t1 where = for xml path('')),''),2,2000)),
           rtrim(substring(isnull((select ','+value2 from YourTable t1 where = for xml path('')),''),2,2000))
      from YourTable t2

    Open in new window

    LVL 4

    Author Closing Comment

    Thank you

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now