Oracle10g SQL 枢轴

2022-01-22 00:00:00 pivot sql oracle oracle10g

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

I have a table named TABLE for example looking like:

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 行，并为他们的其他电子邮件添加一个额外的列.感谢您的帮助.

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;

原始 Pivot 示例具有类型且缺少)".尝试以下操作以使枢轴工作:

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

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

相关文章