-
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.
!!
-
Originally Posted by mdvreddy
i suggest to use hierarchical query concepts
Cheers
/MR
Hence the link I posted above !!
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!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|