Recently I wrote a post on how to split a delimited string in SQL. Now I need to do the reverse and take the flat table and create a delimited string in SQL.
Fortunately creating a delimited string in SQL is far simpler than splitting a delimited string. SQL Server has a STRING_AGG function which simplifies this task.
Assuming we have a flat table called CountryUsers with the following structure:
To convert this table into a 2 record table, one for each country, with a comma delimited string of users, run the following SQL:
,STRING_AGG(CAST(a.User as VARCHAR(MAX)),',') WITHIN GROUP (ORDER BY a.User) UsersPerCountry
GROUP BY a.Country
The output from that SQL are 2 comma separated strings:
To delimit the string by an alternative delimited, such as a pipe delimiter or semi colon delimiter, change the comma adjacent to the CAST statement to the alternative delimiter.