SQL - Using OVER (PARTITION BY)

I want to list all phone numbers for a specific employee in one row.

Let's look at my progress with the OVER PARTITION command.

SELECT A.EMPLID, B.PHONE, ROW_NUMBER() OVER (ORDER BY A.EMPLID) as SEQ
FROM PS_PERSON A, PS_PERSONAL_PHONE B
WHERE B.EMPLID = A.EMPLID;

Results:

Now we add PARTITION BY EMPLID

SELECT A.EMPLID, B.PHONE, ROW_NUMBER() OVER (PARTITION BY A.EMPLID ORDER BY A.EMPLID) as SEQ
FROM PS_PERSON A, PS_PERSONAL_PHONE B
WHERE B.EMPLID = A.EMPLID;

Results:

Now we use the original SQL as pseudo-record.

SELECT EMPLID, MAX(PHONE)
FROM
(
SELECT A.EMPLID, B.PHONE, ROW_NUMBER() OVER (PARTITION BY A.EMPLID ORDER BY A.EMPLID) as SEQ
FROM PS_PERSON A, PS_PERSONAL_PHONE B
WHERE B.EMPLID = A.EMPLID
)
GROUP BY EMPLID
ORDER BY EMPLID;

Results:

And finally, we apply DECODE.

SELECT EMPLID, 
MAX(DECODE(SEQ, 1, PHONE, ''))  || 
MAX(DECODE(SEQ, 2, ', ' || PHONE, '')) || 
MAX(DECODE(SEQ, 3, ', ' || PHONE, '')) ||
MAX(DECODE(SEQ, 4, ', ' || PHONE, '')) ||
MAX(DECODE(SEQ, 5, ', ' || PHONE, '')) ||
MAX(DECODE(SEQ, 6, ', ' || PHONE, '')) ||
MAX(DECODE(SEQ, 7, ', ' || PHONE, '')) ||
MAX(DECODE(SEQ, 8, ', ' || PHONE, '')) ||
MAX(DECODE(SEQ, 9, ', ' || PHONE, '')) ||
MAX(DECODE(SEQ, 10, ', ' || PHONE, '')) AS PHONE_NUMBERS
FROM
(SELECT A.EMPLID, B.PHONE, ROW_NUMBER() OVER (PARTITION BY A.EMPLID ORDER BY A.EMPLID DESC) SEQ
FROM PS_PERSON A, PS_PERSONAL_PHONE B
WHERE B.EMPLID = A.EMPLID) 
GROUP BY EMPLID;

Results:

"Great Success!" - Borat