select distinct p.account_number"Лиц.счет.", 
       listagg (z.last_name||' '||substrc(z.name, 1, 1)||'. '||substrc(z.patronymic, 1, 1), '; ')WITHIN GROUP(order by p.account_number) over (partition by p.account_number)"ФИО", 
       r.name"Название региона", t.short_name||' '||c.name"город", e.short_name||' '||s.name"адрес",h.house_number"ном.дома", f.flat_number"номер квартиры",
       count(DISTINCT z.last_name||' '||substrc(z.name, 1, 1)||'. '||substrc(z.patronymic, 1, 1))"жильцы"
from region r,
     cities c,
     street s,
     house h,
     personal_account p,
     citizen z,
     flat_citizen l,
     flat f,
     street_type e,
     city_type t
where r.id=c.region_id and 
      c.id=s.cities_id and 
      s.id=h.street_id and 
      z.id=l.citizen_id and 
      p.account_number=l.account_number and
      c.id=1 and
      s.id= any (50, 504) and
      p.flat_id=f.id and
      h.id=f.house_id and
      t.id=c.city_type_id and
      e.id=s.type_id and
      l.date_end between to_date('01.12.2016', 'dd.mm.yyyy') and to_date('01.12.2100', 'dd.mm.yyyy')
group by p.account_number, z.last_name||' '||substrc(z.name, 1, 1)||'. '||substrc(z.patronymic, 1, 1),r.name, t.short_name||' '||c.name, e.short_name||' '||s.name,
 h.house_number, f.flat_number
order by h.house_number, f.flat_number

listagg (z.last_name||' '||substrc(z.name, 1, 1)||'. '||substrc(z.patronymic, 1, 1), '; ')WITHIN GROUP(order by z.last_name||' '||substrc(z.name, 1, 1)||'. '||substrc(z.patronymic, 1, 1)) over (partition by p.account_number)"ФИО"