Post reply

get all the values in col2 mapping to col1 as comma seperated in the same row

  • November 6, 2007 at 1:46 pm

    #376740

    Hi sql gurus,

    I have a table that has product name and submissionID and some other columns. I am really concerned about these two columns. My task is to get all the submissionIDs for a particular product name and display SubmissionIDs seperated by commas against each product name .

    The tables below might give a better idea

    current scenario:

    Product Name SubmissionID columnC Column D

    AAA 123

    AAA 456

    BBB 111

    ccc 121

    AAA 789

    Expected result:

    Product Name SubmissionID columnC Column D

    AAA 123,456,789

    BBB 111

    CCC 121

    Alicia Rose

  • November 6, 2007 at 9:02 pm

    #748949

    Duplicate post...

    http://qa.sqlservercentral.com/Forums/Topic419299-266-1.aspx

    --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

  • November 8, 2007 at 10:59 am

    #749590

    I was going to respond in the other thread, but since it's in the Data Corruption forum for some reason, I'll toss it in here. If you'd rather not use a function, you can use the FOR XML Path method with an empty wrapping element, like (this example uses Jeff's table from the other thread, so modify to fit your needs):

    SELECT

    ProductName

    ,ConcatSubmissionIDs = Stuff(

    (

    SELECT

    ','+ Cast(SubmissionID AS varchar(10))

    FROM

    yourtable AS yt1

    WHERE

    yt1.ProductName = yt2.ProductName

    FOR XML PATH('')

    ),1,1,''

    )

    FROM

    yourtable AS yt2

    GROUP BY

    ProductName

    ORDER BY

    ProductName

Viewing 3 posts - 1 through 2 (of 2 total)

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