Oracle10g SQL 枢轴

Question

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

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

我想返回类似

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

我想知道如何使用透视来帮助我摆脱重复的 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

我更喜欢使用带有 CASE 表达式的 GROUP BY 解决方案。

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( max(email) FOR emailRank IN (1,2,3));

Answer 2

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

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