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
In the case of Anne, who has TWO pets, Iíd like to get a result set like this:
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.
Sure thing. This is what I ended up with.
No Pets Here though.
LTRIM (MAX (SYS_CONNECT_BY_PATH (sw_system, '/'))KEEP (DENSE_RANK LAST ORDER BY curr), '/') AS SYSTEM
FROM (SELECT investigation_id,
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,
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;