SQL - Finding all child records for a given record

Using the keys of the parent find all the child records:


-- Tables that are associated with a publication.
SELECT DISTINCT RECNAME
FROM sysadm.PSRECDEFN A
WHERE RECNAME LIKE 'GUR%'
AND RECTYPE = 0
-- Key 1.
AND EXISTS
(SELECT 'X'
FROM sysadm.PSKEYDEFN B
WHERE B.RECNAME = A.RECNAME
AND B.FIELDNAME = 'GUR_PBLCTN_REF_NR')
-- Key 2.
AND EXISTS
(SELECT 'X'
FROM sysadm.PSKEYDEFN B
WHERE B.RECNAME = A.RECNAME
AND B.FIELDNAME = 'EFFDT')


SQL - Multiple rows into one. Transpose SQL Results.

I've got Table.Person and Table.Pet. Each person has multiple pets.

How do I return one row for each person with all their pets comma delimited in one column?

SELECT
PERSON_ID
, TRIM(',' FROM TRIM(' ' FROM
MAX(DECODE(SEQ, 1, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 2, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 3, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 4, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 5, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 6, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 7, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 8, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 9, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 10, PET_NAME || ', '))
)) AS PET_NAMES
FROM
(SELECT
A.PERSON_ID
, A.NAME AS PERSON_NAME
, B.NAME AS PET_NAME
, ROW_NUMBER() OVER (PARTITION BY PERSON_ID ORDER BY PERSON_ID DESC) SEQ
FROM
PERSON A
, PET B
WHERE B.PERSON_ID = A.PERSON_ID)
GROUP BY PERSON_ID