Oracle10g SQL pivot

Question

例如，我有一个名为table的表，如下所示：

ID  | email
--------------
1   |  a@a.com 
1   |  b@b.com
2   |  c@c.com
3   |  d@d.com
3   |  e@e.com

我想返回一些类似的东西

ID | email1 | email2
--------------------
1  | a@a.com| b@b.com
2  | c@c.com|
3  | d@d.com| e@e.com

我想知道如何使用旋转来帮助我摆脱重复的ID行，只需为他们的其他电子邮件添加一个额外的列。谢谢你的帮助。

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));

编辑:由于海滩的回答，修复了上面的问题

Answer 1

我更喜欢将GROUP BY解决方案与CASE表达式一起使用。

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;

原始透视示例具有类型，但缺少")“。尝试执行以下操作以使pivot正常工作：

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

Answer 2

您可以使用一个过程或group by rownum和decode的组合。就我个人而言，我发现过程方法更干净。

请参阅：http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:15151874723724