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.