Oracle10g SQL pivot

问题:

I have a table named TABLE for example looking like:

ID  | email
--------------
1   |  [e-mail] 
1   |  [e-mail]
2   |  [e-mail]
3   |  [e-mail]
3   |  [e-mail]

and I would like to return something like

ID | email1 | email2
--------------------
1  | [e-mail]| [e-mail]
2  | [e-mail]|
3  | [e-mail]| [e-mail]

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

回答1:

I prefer using the GROUP BY solution with CASE expression.

SELECT 
    id,
    MAX(CASE WHEN emailRank = 1 THEN email END) AS [1],
    MAX(CASE WHEN emailRank = 2 THEN email END) AS [2],
    MAX(CASE WHEN emailRank = 3 THEN email END) AS [3],
    MAX(CASE WHEN emailRank = 4 THEN email END) AS [4]
FROM (
    SELECT
        id, 
        email, 
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY email) AS emailRank
    FROM TABLE
)
GROUP BY id;

Original Pivot example had type and missing ")". Try the following to get pivot working:

pivot( max(email) FOR emailRank IN (1,2,3));

回答2:

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