如果需要三个，可以使用条件聚合

select id,
       max(case whens seqnum = 1 then email end) as email1,
       max(case whens seqnum = 2 then email end) as email2,
       max(case whens seqnum = 3 then email end) as email3
from (select t.*, row_number() over (partition by id order by email) as seqnum
      from t
     ) t
group by id;

如果您不关心列（因为这样的东西不能缩放），请考虑 listagg 它允许您“串联”多达4000个字符的电子邮件地址。
例如（请注意，#1-10行表示样本数据；您的表中已经有了它，因此您可能需要考虑的查询从第11行开始）：

SQL> with test (id, email) as
  2    (select 27, 'jd@yahoo.com'        from dual union all
  3     select 27, 'johndoe47@gmail.com' from dual union all
  4     select 27, 'MrDoe@hotmail.com'   from dual union all
  5     --
  6     select 25, 'little@hotmail.com'  from dual union all
  7     select 25, 'foot@gmail.com'      from dual union all
  8     --
  9     select 13, 'bigfoot@net.hr'      from dual
 10    )
 11  select id,
 12    listagg(email, ', ') within group (order by email) email
 13  from test
 14  group by id
 15  order by id;

        ID EMAIL
---------- ----------------------------------------------------------------------
        13 bigfoot@net.hr
        25 foot@gmail.com, little@hotmail.com
        27 MrDoe@hotmail.com, jd@yahoo.com, johndoe47@gmail.com

SQL>

或者，稍微漂亮一点 rpad ):

<snip>
 11  select id,
 12    listagg(rpad(email, 20, ' '), ' | ') within group (order by email) email
 13  from test
 14  group by id
 15  order by id;

        ID EMAIL
---------- ----------------------------------------------------------------------
        13 bigfoot@net.hr
        25 foot@gmail.com       | little@hotmail.com
        27 MrDoe@hotmail.com    | jd@yahoo.com         | johndoe47@gmail.com

SQL>

您可以生成电子邮件类别（email1、email2等），然后在pivot子句中使用它们。不过，请确保在pivot中编写了足够多的类别(例如，当使用以下查询时：如果某人有5个电子邮件地址，则其中一个地址将不在结果集中。）

select *
from (
  select id, email
  , 'Email' || row_number() over ( partition by id order by email ) emailcategory_
  from emailaddresses
)
pivot (
  max( email ) for ( emailcategory_ ) in (
    'Email1' as "Email1"  -- optional: use AS ... here to "beautify" the column headings
  , 'Email2' as "Email2"
  , 'Email3'
  , 'Email4' 
  )
) ;

在这儿摆弄。