Post reply

Rollup information within a table

  • July 25, 2007 at 6:51 am

    #177680

    How do I rollup information from separate rows in a table into just one row within a table?

  • July 25, 2007 at 7:33 am

    #722275

    Is GROUP BY what you're looking for?

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

  • July 25, 2007 at 7:37 am

    #722277

    I don't think so.  I have multiple rows within a table.  These rows contain various pieces of information.  I want to rollup all of this information to only show up in one row within the table.

  • July 25, 2007 at 8:14 am

    #722300

    Hi Laura! Can you be a little more specific?

    1. You want to 'marry' unlike details correlating to the same core content (matching foreign keys [FK]) and create a table, rather than a view putting everything in one place.

    2. You are doing a conversion from one DBMS to another and wish to change the underlying database structure.

    3. You are trying to 'refine' the table content to facilitate processing and help make programmers' lives easier.

    I could go on, there's another angle to this as well:

    1. You would like us to provide you samples of code, or exact code that will do what you need.

    2. You want references to web pages or other materials that will teach you what you want to know, so you can be more effective and understand the theory behind what you are doing.

    So, give us some hints how we can best help you get where you want to be or go.

    Also, you may want to try to search the forums for coding examples. For instance, searching just the forums on 'JOIN' will give you some interesting results. You will also find threads that will show you what kind of information to give us, if you have the time.

  • July 25, 2007 at 8:25 am

    #722312

    I have a table which contains a history of updated information.  Within this table there are fields called OriginalEmail, CurrentEmail, OriginalPhone CurrentPhone, OriginalAddress, CurrentAddress, etc.

    Right now I have a row in the table where the OriginalEmail & Current Email, but all other fields are blank.  I then have in the next row where OriginalPhone & CurrentPhone have values, but all other fields are blank.  Yet another row where OriginalAddress & CurrentAddress have values but all are blank.  While these rows all have the same unique identifier.

    I want to merge these rows into one, so there is only one row for a particular unique identifier where OriginalEmail, CurrentEmail, OriginalPhone CurrentPhone, OriginalAddress, CurrentAddress, etc. are all populated.

    Currently, I am having to do a manual update so only one row has all the information and then deleted the other rows.

    I would like an easier and faster way to do this.

    Ooops-- didn't mean to post this 2x, thanks for letting me know.

  • July 26, 2007 at 5:49 am

    #722530

    This is a pretty open-ended post so you will probably find lots of answers coming at you soon.

    One possibility is to use group by and the MAX aggregate.  Because any value is greater than (or less than for that matter) NULL, your null columns will be removed from your aggregate columns.

    Here is a simple example:

    create table #tmp (ID INT, Val1 VARCHAR(10), Val2 VARCHAR(10))

    INSERT #tmp SELECT 1, 'A', NULL

    INSERT #tmp SELECT 1, NULL, '1'

    INSERT #tmp SELECT 2, 'B', NULL

    INSERT #tmp SELECT 2, NULL, '2'

    SELECT ID, MAX(Val1) AS Val1, MAX(Val2) AS Val2 FROM #tmp GROUP BY ID

  • July 26, 2007 at 5:51 am

    #722532

    Thanks!!

Viewing 7 posts - 1 through 6 (of 6 total)

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