DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: 2 rows become 1......

  1. #1
    Join Date
    Sep 2007
    Posts
    36

    2 rows become 1......

    Hi All,
    I’m hoping you can help me with a query. I’m trying to amalgamate the data from 2 rows into 1 row if certain conditions are met. I’m not sure this is possible with a query, or if I am going to have to write some code….
    Anyway, here is a more detailed description. Suppose I have a query that returns a persons name, and the name of their pet/s

    Name PetName
    ------ -----------
    John Fluffy
    Sue Floppy
    Anne Spot
    Anne Flipper

    In the case of Anne, who has TWO pets, I’d like to get a result set like this:

    Name PetName
    ------ -----------
    John Fluffy
    Sue Floppy
    Anne Spot / Flipper


    Is this formatting possible?
    Thanks,
    J

  2. #2
    Join Date
    Apr 2006
    Posts
    377
    What have you tried so far?

    You may want to consider SYS_CONNECT_BY_PATH.

  3. #3
    Join Date
    Dec 2007
    Posts
    82
    i suggest to use hierarchical query concepts

    Cheers
    /MR

  4. #4
    Join Date
    Sep 2007
    Posts
    36
    I thought about using 'connect by prior', but I thought this was only if your data was hierarchical in nature within the same table?
    In my example I have 2 tables, People and Pets.
    The Pets table contains an owner_id field.
    So when I join people.person_id = pets.owner_id I'm getting 2 rows for the Anne example above.
    Is it possible to use the sys_connect_by_path in this instance to return only a single row?
    Thanks for your help so far.
    J

  5. #5
    Join Date
    Sep 2007
    Posts
    36
    Yep, thanks for the pointer, got this working with SYS_CONNECT_BY_PATH.
    !!

  6. #6
    Join Date
    Apr 2006
    Posts
    377
    Quote Originally Posted by mdvreddy
    i suggest to use hierarchical query concepts

    Cheers
    /MR
    Hence the link I posted above !!

    Quote Originally Posted by jay_l_a
    Yep, thanks for the pointer, got this working with SYS_CONNECT_BY_PATH.
    !!
    Glad that helped...but c'mon...no fun to take and not give back. You should post your query so others may benefit.

  7. #7
    Join Date
    Sep 2007
    Posts
    36
    Sure thing. This is what I ended up with.
    No Pets Here though.

    SELECT investigation_alias,
    investigation_site_no,
    LTRIM (MAX (SYS_CONNECT_BY_PATH (sw_system, '/'))KEEP (DENSE_RANK LAST ORDER BY curr), '/') AS SYSTEM
    FROM (SELECT investigation_id,
    investigation_alias,
    investigation_site_id,
    investigation_site_no,
    sw_system,
    ROW_NUMBER () OVER (PARTITION BY investigation_alias, investigation_site_no ORDER BY sw_system) AS curr,
    ROW_NUMBER () OVER (PARTITION BY investigation_alias, investigation_site_no ORDER BY sw_system) - 1 AS prev
    FROM (SELECT DISTINCT inv.investigation_id,
    i.investigation_alias,
    se.investigation_site_id,
    inv.investigation_site_no,
    sw.name,
    sw.name sw_system
    FROM comp.investigation i
    INNER JOIN comp.investigation_site inv
    ON i.investigation_id = inv.investigation_id
    INNER JOIN comp.site_equipment se
    ON se.investigation_site_id = inv.investigation_site_id
    INNER JOIN comp.equipment e
    ON e.equipment_id = se.equipment_id
    INNER JOIN comp.software sw
    ON e.software_id = sw.software_id
    ))
    GROUP BY investigation_alias, investigation_site_no
    CONNECT BY prev = PRIOR curr AND investigation_site_id = PRIOR investigation_site_id
    START WITH curr = 1;

  8. #8
    Join Date
    Apr 2006
    Posts
    377
    Looks good Jay. Thanks for posting back your results!

Posting Permissions

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


Click Here to Expand Forum to Full Width