devxlogo

Using FOR XML PATH and STUFF SQL String Concatenation

Using FOR XML PATH and STUFF SQL String Concatenation

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.

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist