declare
CURSOR cur IS
select distinct(roomno) from cur_table;
CURSOR inner;
namelist varchar2(100);

begin;

create table table1 (
roomno varchar2(50),
name varchar2(100)
);

for each record in roomno do
inner IS
select distinct(name) from cur_table where roomno = record.roomno
for each inner_record in inner do
namelist := namelist || ',' || inner_record.name;
end loop;

insert into table1 values (record.roomno, namelist);
end loop;

commit;

end;