Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Dec 6, 2016

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:

--Concat
SELECT 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.

Hannes du Preez
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date