Oracle10g SQL枢轴 [英] Oracle10g SQL pivot

问题描述

我有一个名为TABLE的表，例如:

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]

我想返回类似的内容

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

我想知道如何使用透视来帮助我摆脱重复的ID行，而只为其他电子邮件添加一个额外的列.感谢您的帮助.

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

由于海滩的回答，上面已修复

fixed above thanks to beach's answer

推荐答案

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

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

这篇关于Oracle10g SQL枢轴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！