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

0 comments: