You can use the FOR XML PATH SQL Statement to join or concatenate multiple column values into single row. You could then use STUFF to remove the first ‘,’ (now redundant) after the string has been concatenated.

Create a quick table, listing cities from different countries. You will notice that there are cities that share the same country code:

DECLARE @A_TEMP_TABLE_USED_AS_EXAMPLE TABLE(CountryCode INT, City VARCHAR(50))INSERT @A_TEMP_TABLE_USED_AS_EXAMPLE(CountryCode, City)VALUES(1, 'Johannesburg'), (1, 'Cape Town'), --South Africa(2, 'New York'), (2, 'Washington'), --USA(3, 'Paris') ,(3, 'Nice'), --France(4, 'Rome'), (4, 'Bologna'), --Itlay(5, 'Athens'), (5, 'Volos') --Greece

Add the Following SELECT Query to concatenate the Cities belonging to the same country code onto one line:

--ConcatSELECT DISTINCT 	CountryCode,	STUFF((SELECT ',' + City	FROM @A_TEMP_TABLE_USED_AS_EXAMPLE [TABLE1]	WHERE TABLE1.CountryCode = TABLE2.CountryCode	FOR XML PATH('')), 1, 1,'') 		FROM @A_TEMP_TABLE_USED_AS_EXAMPLE [TABLE2] 

This concatenates all rows with the same country code. STUFF is then used to remove the redundant comma at the start of the row.