Oracle10g SQL pivot

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

编辑：由于海滩的答案修复上面

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!