Post reply

Rearranging data

  • May 21, 2009 at 4:58 pm

    #384638

    ColA ColB

    A 1

    B 20

    B 23

    C 2

    C 23

    D 5

    D 56

    D 67

    My result should be

    ColA ColB

    A 1

    B 20,23

    C 2,23

    D 5,56,67

    There should be a unique ColA value and corresponding colB values as a single row.

  • May 21, 2009 at 5:08 pm

    #998611

    This is a cross tab report. Search on that and make an attempt. We can help if you can't figure it out from examples.

  • May 21, 2009 at 6:58 pm

    #998637

    Actually, because all of the row data is to be stored as CSV's in a single column, it's a concatenation job. Please see the following article for how to do it and some of the caveats involved. It also includes and XMP Path method that works really well in 2k5 and probably as well in 2k8.

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

    That's if you really want to do this. Although there are some very rare ligitimate uses, concatenation of data into a single CSV is usually a mistake or bad set of requirements especially if someone insists the data be stored in a table that way.

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

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

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