1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| with data as (
select 1 as sejo_num_sej, 'Dupont' as mede_nom_usu, 'Acte A' as acsa_code_d from dual
union all
select 1 as sejo_num_sej, 'Dupont' as mede_nom_usu, 'Acte B' as acsa_code_d from dual
union all
select 1 as sejo_num_sej, 'Dupont' as mede_nom_usu, 'Acte B' as acsa_code_d from dual
union all
select 1 as sejo_num_sej, 'Durant' as mede_nom_usu, 'Acte B' as acsa_code_d from dual
union all
select 1 as sejo_num_sej, 'Durant' as mede_nom_usu, 'Acte C' as acsa_code_d from dual
)
select sejo_num_sej as sejour, json_arrayagg(bilan_medecin returning clob) as bilan from (
select sejo_num_sej, json_object( 'medecin' value mede_nom_usu, 'prestations' value prestations) as bilan_medecin from
(
select sejo_num_sej, mede_nom_usu, json_arrayagg(acsa_code_d order by acsa_code_d returning clob) as prestations
from
(
select distinct sejo_num_sej, mede_nom_usu, acsa_code_d from data
)
group by sejo_num_sej, mede_nom_usu
)
)
group by sejo_num_sej
; |
Partager