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
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
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;
Reply With Quote
Originally Posted by mdvreddy
Bookmarks