Quick concatenation of rows in T-SQL using FOR XML PATH

February 9th, 2017
by Kristof

If you want to quickly concatenate a resultset, here's a quick and easy way to do it.

SELECT 
	ProductType AS [data()]
FROM
	dbo.Product
FOR XML PATH ('')

SELECT 
	ProductType AS [text()]
FROM
	dbo.Product
FOR XML PATH ('')

The example with data() will yield a space-delimited list of values. The example with text will yield a continuous list of values. Here's how to supply a delimiter.

SELECT 
	',' + ProductType AS [data()]
FROM
	dbo.Product
FOR XML PATH ('')

You can also use DISTINCT to have recurring values appear only once.

SELECT 
	DISTINCT 
	',' + ProductType AS [data()]
FROM
	dbo.Product
FOR XML PATH ('')

You can also concatenate multiple fields. The example below also shows how to remove the leading delimiter.

SELECT
	STUFF(
		(SELECT 
			DISTINCT 
			';' + ProductType + ': ' + ProductTypeCategory AS [data()]
		FROM
			dbo.Product
		FOR XML PATH ('')), 1, 1, '')

Enjoy!

Posted in Sql | Comments (0)

No comments yet

Leave a Reply

SEO Powered by Platinum SEO from Techblissonline