Concatenates all email addresses of active users from table into mailing list

mailing_list.sql

WITH
  email_list (concatenated_emails)
AS 
  ( 
    SELECT 
      ADDEML_0 + ';'
    FROM
      AUTILIS
    WHERE 
      ENAFLG_0 = 2
    AND 
      USR_0 NOT IN ('ADMIN', 'ND1', 'ADMCA')
    GROUP BY 
      ADDEML_0
    FOR XML PATH('')
  )
  SELECT 
    concatenated_emails
  FROM 
    email_list
    
/* Returned result set */

concatenated_emails
---------------------------
[email protected];[email protected];[email protected]