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

What have you tried so far?

You may want to consider SYS_CONNECT_BY_PATH.

i suggest to use hierarchical query concepts

Cheers
/MR

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

Yep, thanks for the pointer, got this working with SYS_CONNECT_BY_PATH.
!!:)

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.

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;

Looks good Jay. Thanks for posting back your results!