Results 1 to 4 of 4

Thread: "merging" rows

  1. #1
    Join Date
    Feb 2004
    Posts
    6

    Unanswered: "merging" rows

    Hi,

    by sql-querying (oracle 9.2.0.4) over several tables I get the result select wich has one flaw: There are rows belonging to each other but which stand below each other. A realistic example: One sample was analysed for chemistry and pestizides. This gives me two rows.
    -----------------------------------------------------------------------------
    First row: all chemistry data, but NULL in the pestizide columns.
    Second row: all pestizides, but NULL in the chemistry columns.
    ------------------------------------------------------------------------------
    Better and more comfortable: one row containing all data.
    ------------------------------------------------------------------------------

    Any idea to "merge" those two first rows together using sql? If you think it's not, I would appreciate advices on which paths I have to follow then, lets say pl/sql, what ever. I'm lacking of much experiences on the very complex orcale DB. The whole thing is for a small web-interface for our spreaded working group. Nothing big, just looking for wells, samples, analyses. Download the selects as *.csv files, thats it. So under circumstances it might is better to do the above in php?

    Thank you for help,
    Torsten

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: "merging" rows

    If what you are seeing is pairs of rows like this:
    PHP Code:
    A B C P Q R X Y Z
    - - - - - - - - -
    1 2 3 4 5 6
    1 2 3       7 8 9 
    ... and what you want to see it this:
    PHP Code:
    A B C P Q R X Y Z
    - - - - - - - - -
    1 2 3 4 5 6 7 8 9 
    ... then you can do it like this:
    PHP Code:
    select t1.at1.bt1.c,
           
    max(t1.p), max(t1.q), max(t1.r),
           
    max(t2.x), max(t2.y), max(t2.z)
      
    from t1 join t2 on t1.t2.a
                     
    and t1.t2.b
                     
    and t1.t2.c
     group by t1
    .at1.bt1.c
    You may want to use a full outer join if you can get either record without the other.

  3. #3
    Join Date
    Feb 2004
    Posts
    6

    Re: "merging" rows

    Thank you,
    I obviously didn't asked comprehensively. So is there so far no general sql-way to do it as it seems from your reply? I didn't told the whole thing, sorry. Because the number of rows belonging to each other is not known, exept that it could be from 1 to 3. Also analysed chemical parameter can vary depending on the selection at the webside. My intention now is to give this "merging" job to php if there is no other more or less simple solution. Right now the database is not that loaded, so that this way might not be that seriously time consuming.

    If you have still a better suggestion, I'd be happy,
    Torsten

    > You may want to use a full outer join if you can get either record
    > without the other.

    Something like a full outer "self" join? I don't know what you mean (probably a consequence of my un-native english).

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: "merging" rows

    Perhaps if you post some sample data and desired output someone can suggest the way?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •