我只需要为每一个派生一个行号，然后使用条件聚合。

select
  person,
  max(case when rn = 1 then hobby else null end) as hobby1,
  max(case when rn = 2 then hobby else null end) as hobby2,
  max(case when rn = 3 then hobby else null end) as hobby3
from 
(
  select
    person,
    hobby,
    row_number() over (partition by person order by hobby) as rn
  from foo
) t
group by person;

Fiddle

这是使用PIVOT的另一种方法：

with cte as (
  select person, hobby,
  row_number() over (partition by person order by hobby) as rn
  from mytable
)
select *
from cte
pivot 
(  max(HOBBY)
   for rn in (1 as "hobby1", 2 as "hobby2", 3 as "hobby3", 4 as "hobby4", 5 as "hobby5", 6 as "hobby6")
)

结果：

PERSON  hobby1      hobby2     hobby3   hobby4  hobby5  hobby6
123     Gardening   Hiking     null     null    null    null
176     Reading     null       null     null    null    null
345     Football    Gardening  Hiking   null    null    null

Demo here