2 rows become 1......
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
Is this formatting possible?
What have you tried so far?
You may want to consider SYS_CONNECT_BY_PATH.
i suggest to use hierarchical query concepts
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.
Yep, thanks for the pointer, got this working with SYS_CONNECT_BY_PATH.
Hence the link I posted above !!
Originally Posted by mdvreddy
Glad that helped...but c'mon...no fun to take and not give back. You should post your query so others may benefit.
Originally Posted by jay_l_a
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;
Looks good Jay. Thanks for posting back your results!
Click Here to Expand Forum to Full Width