SQL Group employees into teams

create table team(ename varchar2(30), city varchar2(100));

insert into team values('Veda','Pune');

insert into team values('Shruthi','Pune');

insert into team values('Apoorva','Pune');

insert into team values('Karthik','Pune');

insert into team values('Jhon','Pune');

insert into team values('Shyam','Delhi');

insert into team values('Ram','Delhi');

insert into team values('Somen','Kolkata');

commit;

WITH d AS (

SELECT ename,

   city,

   ceil(ROW_NUMBER() OVER(PARTITION BY city ORDER BY ROWNUM) / 2) t 

FROM

team

)

SELECT

city,

listagg(ename, ',') team_members,

'Team'|| ROW_NUMBER() OVER(ORDER BY city) team_name

FROM d

GROUP BY city,t

ORDER BY 3;