I’ve recently written 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.

Fortunately creating a delimited string is far simpler than splitting a delimited string.

Assuming we have a flat table called CountryUsers with the following structure:

CountryUser
UKBob
UKFred
UKBill
USAMike
USALou
USACarol
USASam

To convert this table into a 2 record table, one for each country, with a comma delimited string of users, run the following SQL:

SELECT DISTINCT a.Country

,STUFF(

(SELECT ‘,’ + b.[User]

FROM CountryUsers b

WHERE b.Country = a.Country

FOR XML PATH ())

,1,1,) as UsersPerCountry

FROM CountryUsers a

 

The output from that SQL are 2 comma separated strings:

CountryUsersPerCountry
UKBob,Fred,Bill
USAMike,Lou,Carol,Sam

To delimit the string by an alternative delimited, such as a pipe delimiter or semi colon delimiter, change the comma next to the SELECT in the STUFF function to the alternative delimiter, for example SELECT ‘|’ +…