WITH ListadoPonencias AS (
 select tc.idregistro as idcongreso, tcl.idregistro as idarea, tcl.nombre_area ,
 tp.idregistro ,codigo ,tema , resumen, palabra_clave,
 tpa.orden,
    tpa.id_tipo_categoria,
    CASE
      WHEN tpa.id_personal_sga IS NULL THEN concat(tpe.nombres, ' ', tpe.apellido1, ' ', tpe.apellido2)
      ELSE concat(p.nombres, ' ', p.apellido1, ' ', p.apellido2)
    END AS autor,
    tbi.nombre AS filiacion,
    CASE
      WHEN tpa.id_personal_sga IS NULL THEN tpe.correo
      ELSE p.correo_personal_institucional
    END AS correo,
    ug.nombre AS pais
from esq_conv_2020.tbl_ponencias tp
join esq_conv_2020.tbl_congreso_linea tcl on tcl.idregistro = tp.id_congreso_linea
join esq_conv_2020.tbl_congreso tc on tc.idregistro = tcl.id_congreso
join esq_conv_2020.tbl_ponencias_autores tpa on tpa.id_ponencia = tp.idregistro
  LEFT join esq_datos_personales.personal p ON p.idpersonal = tpa.id_personal_sga
  LEFT join esq_conv_2020.tbl_personal_ext tpe ON tpe.idregistro = tpa.id_personal_tmp
  LEFT join esq_conv_2020.tbl_banco_instituciones tbi ON tbi.idregistro = tpa.id_filiacion
  LEFT join esq_catalogos.ubicacion_geografica ug ON ug.idubicacion_geografica = tbi.enum_pais::numeric
where tc.id_convencion  = 9
and tp.id_estado in (61,63,64)
and tp.codigo not in('CCIUTM-REG-ADM')
and tc.idregistro not in (128)
order by tc.idregistro,tcl.nombre_area,tc.nombre, tcl.nombre_area , tp.tema,tpa.orden, tpa.idregistro)
select idcongreso, idarea,nombre_area,
u.idregistro ,codigo ,tema , resumen, palabra_clave,
array_to_string(ARRAY_AGG(concat(esq_conv_2020.capitalize_except(u.autor), CASE WHEN u.id_tipo_categoria = 45 THEN '*' END) ORDER BY u.orden ), ', ') AS autores,
array_to_string(ARRAY_AGG(CASE WHEN u.id_tipo_categoria = 45 THEN concat(esq_conv_2020.capitalize_except(filiacion),', ',pais,', ',correo) END), ', ') AS infoautores
FROM
  ListadoPonencias u
group by idcongreso, idarea,nombre_area,u.idregistro ,codigo ,tema , resumen, palabra_clave