Post reply

Group by and Concatenate just one field?

  • February 22, 2011 at 4:44 am

    #388910

    Hi,

    I have a result set containing several fields, one of which 'Comment' is a varchar field. I would like to group by all the other fields and concatenate all the 'comment' values into just one string. Can anyone advise how I might do this? - So rather than aggregating the field, I just want the values concatenating to each other (with possible the addition of a 'space' delimiter).

    I've not posted any DDL code, as thought the answer could possible be given in principle.

    Any help greatly appreciated.

    Regards - Jason

  • February 22, 2011 at 11:59 am

    #1290022

    Search this site for "concatenate FOR XML PATH".


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 23, 2011 at 12:26 am

    #1290245

    Thanks for the pointer, Lutz. I've searched SSC as suggested, and it seems that the XML functionality is only available in SS2005 and later. Unfortunately, although I'm using 2008 SS app, the server itself is 2000 so I'm limited to 2000 syntax.

    Regards - Jason

  • February 27, 2011 at 5:12 pm

    #1292093

    jason-772533 (2/23/2011)

    Thanks for the pointer, Lutz. I've searched SSC as suggested, and it seems that the XML functionality is only available in SS2005 and later. Unfortunately, although I'm using 2008 SS app, the server itself is 2000 so I'm limited to 2000 syntax.

    Regards - Jason

    Then the following article will help, a lot!

    http://qa.sqlservercentral.com/articles/Test+Data/61572/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 28, 2011 at 2:31 am

    #1292195

    Thanks Jeff - really interesting article. I kind of agree with you in the 1st paragraph - I'd rather not be doing it, but someone wants the 'comments' concatenated rather than 'on seperate rows'. If only people would take the data in the way it was intended !

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply