oracle - Oracle10g SQL pivot

Question

I have a table named TABLE for example looking like:

ID  | email
--------------
1   |  [email protected] 
1   |  [email protected]
2   |  [email protected]
3   |  [email protected]
3   |  [email protected]

and I would like to return something like

ID | email1 | email2
--------------------
1  | [email protected]| [email protected]
2  | [email protected]|
3  | [email protected]| [email protected]

I was wondering how I could use pivoting to help me get rid of duplicate ID rows and just add an extra column for their other emails. Thanks for the help.

SELECT id, email1, email2, email3
FROM (
SELECT id, 
        email, 
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY email) AS emailRank
FROM TABLE
) 
pivot( max(email) FOR emailRank IN (1 as email1, 2 as email2, 3 as email3));

Edit: fixed above thanks to beach's answer

Answer 1

You can use a procedure or a combination of group by rownum and decode. Personally, I find the procedure approach cleaner.

See: http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:15151874723724