with a(id,ip)
as (      select 'id1','1.1.1.1' from dual
union all select 'id1','1.1.1.2' from dual
union all select 'id1','1.1.1.9' from dual
union all select 'id2','1.1.1.3' from dual
union all select 'id2','1.1.2.3' from dual
union all select 'id3','1.3.1.3' from dual
union all select 'id4','1.4.1.3' from dual
)
select id, '['||LISTAGG( ip, '],[' ) WITHIN GROUP (ORDER BY ip)||']' ips
     , count(*) ipcnt
  from a
 group by id
having sum(1) > 1