Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table mytable( PersID int,   PersItem varchar(50));

insert into mytable values(1,'apple');
1 rows affected

insert into mytable values(1,'orange');
1 rows affected

insert into mytable values(2,'peach');
1 rows affected

insert into mytable values(2,'mango');
1 rows affected

insert into mytable values(2,'grape');
1 rows affected

select * from mytable


PERSID
PERSITEM


1
apple

1
orange

2
peach

2
mango

2
grape


select * from 
(select PersID,persitem,row_number()over(partition by persid order by rownum) rn from mytable)
pivot
(
max(persItem) for rn in (1,2,3)
)


PERSID
1
2
3


1
apple
orange
null

2
peach
mango
grape


select PersID,"1" PersItem,"2" PersItem, "3" PersItem from 
(select persid,persitem,row_number()over(partition by persid order by rownum) rn from mytable)
pivot
(
max(persItem) for rn in (1,2,3)
)


PERSID
PERSITEM
PERSITEM
PERSITEM


1
apple
orange
null

2
peach
mango
grape