I need help creating an 'update' to aggregate or concatenate several records from a table into a field in another table

1

I need help creating an 'update' to aggregate or concatenate several records from a table into a field in another table.
For example, there is a customer who has several emails, these emails are in a separate table and now I need that in the main table, the emails are all aggregated in a customer field, all separated by ';' I already tried with the following UPDATE but it doesn't work.

Update Clientes set CDU_EmailMSS = (
select
STRING_AGG(LinhasContactoEntidades.Email, ';') EmailMSS
from Clientes
left join LinhasContactoEntidades on Clientes.Cliente = LinhasContactoEntidades.Entidade
where LinhasContactoEntidades.TipoContacto='FATURAÇÃO'
group by clientes.Cliente
)

2

Welcome!

And please try this:


Update C
SET CDU_EmailMSS = CA1.EmailMSS
FROM Clientes C
OUTER APPLY (
    select STRING_AGG(LinhasContactoEntidades.Email, ';') EmailMSS
    from LinhasContactoEntidades LCE
    where C.Cliente = LCE.Entidade AND LCE.TipoContacto='FATURAÇÃO'
    group by LCE.Entidade
) AS CA1
1 Like
3

Excellent, worked perfectly!!
Thanks a lot for the help.