drop table if exists tmp1 ;
create temporary table tmp1 as
(
select distinct branch_code, subscriber_no, late3_date
from ci_dt_loan
where late3_date is not null
) ;
select t1.branch_code, t1.subscriber_no, t2.subscriber_name, t2.category, t2.expiry_date, replace(replace(array_agg(late3_date order by late3_date)::text, '{', ''), '}', '') as dates_3e_avis
from tmp1 as t1
	inner join ci_dt_subscriber as t2 on t1.subscriber_no = t2.subscriber_no
group by 1, 2, 3, 4, 5
order by 1, 3 ;

Résultat :

branch_code subscriber_no subscriber_name expiry_date category dates_3e_avis
BB EXXXXXX NOM, Prénom 2017-10-29 EL 2017-05-11
BB EXXXXXX NOM, Prénom 2018-03-02 EL 2017-11-24
BB EXXXXXX NOM, Prénom 2016-05-05 EL 2016-10-11,2016-10-28
BB EXXXXXX NOM, Prénom 2016-02-18 EL 2015-04-23
BB EXXXXXX NOM, Prénom 2015-09-26 EL 2015-04-23
BB EXXXXXX NOM, Prénom 2018-10-12 ML 2018-06-27
BB EXXXXXX NOM, Prénom 2019-02-26 EL 2017-10-05
BB EXXXXXX NOM, Prénom 2019-02-26 EL 2017-10-05
BB EXXXXXX NOM, Prénom 2018-01-27 ML 2017-10-05

 

Publicités