STRING_AGG()

SQL Server STRING_AGG() function to concatenate rows of strings into one string with a specified separator. It does not add the separator at the end of the result string. Syntax is STRING_AGG ( input_string, separator ) [ order_clause ]

• input_string is any type that can be converted VARCHAR and NVARCHAR when concatenation.
• separator is the separator for the result string. It can be a literal or variable.
• order_clause specifies the sort order of concatenated results using WITHIN GROUP clause.

To generate lists of emails of customers by the city

SELECT
    city, 
    STRING_AGG(email,';') email_list
FROM
    sales.customers
GROUP BY
    city;

To sort the email list, you use the WITHIN GROUP clause

SELECT
    city, 
    STRING_AGG(email,';') 
        WITHIN GROUP (ORDER BY email) email_list
FROM
    sales.customers
GROUP BY
    city;