How to combine values from multiple rows of a single column (T-SQL, Microsoft SQL Server, FOR XML PATH, CSV ).

The Problem:

I need a way to roll-up multiple rows into one row and one column. In essence I needed to convert something that looked like this:

Original SubCategories Listing

USE AdventureWorks2012;

SELECT C.Name,

SC.Name

FROM [Production].[ProductCategory] C

LEFT JOIN [Production].[ProductSubcategory] SC

ON C.ProductCategoryID = SC.ProductCategoryID;

Into something that looks like this:

SubCategories formatted data

The Solution:

The solution I will show will make use of the FOR XML PATH clause and STUFF commands in T-SQL.

FOR XML PATH Clause

Will will make use of the XML functionality of SQL Server to amalgamate the series of rows into one row. In order to create a XML string you must append the FOR XML command after any regular query. There are four options to append to the FOR XML PATH command which will alter what the result will look like. These options are either RAW, AUTO, EXPLICIT, or PATH. For this solution we will use the PATH option (will explain these four option in more detail in a later post).

The following query shows a regular query which retrieves the subcategory name in an XML string

1	SELECT Name from [Production].[ProductSubcategory] FOR XML PATH('');

The results look like the following:

You will notice that you get a formatted XML string without a root node. To convert this list to a CSV string we just need to append a comma (“,”) to the name field.

The following sql query demonstrates the XML string being converted to a CSV string:

1	SELECT ',' + Name from [Production].[ProductSubcategory] FOR XML PATH('');

CSV Result

This is getting a bit closer to the results we want, but we if you haven’t noticed the string begins with a comma which we should remove. In order to do this we will use the t-sql STUFF function

STUFF() Function

The syntax of the t-sql STUFF function looks like the following:

1	STUFF ( character_expression , start , length , replaceWith_expression )

To remove the first character of the string all we need to so is do something like the following:

1	Stuff( result of the query listed above, 1, 1, '')

This says, starting with the first 1 character place, replace 1 characters with nothing ''.

What does the final solution look like?

USE AdventureWorks2012

SELECT CAT.Name AS [Category],

SubCatName =

STUFF(( SELECT ','+ SUB.Name AS [text()]

FROM Production.ProductSubcategory SUB

WHERE

SUB.ProductCategoryID = CAT.ProductCategoryID

FOR XML PATH('')

), 1, 1,'')

FROM Production.ProductCategory CAT;

: SubCategories formatted data

References

Comments

waleed ahmed wadkar says

April 23, 2014 at 3:18 pm

It works easily for One column, what about another column consisting multiple brands for each category… add the brand column to existing table ..having multiple brands in each row using stuff function ..please do show the method

waleed ahmed wadkar says

April 23, 2014 at 3:42 pm

Hi All,

Anyways, I figured it out using Stuff funtion with For XML path I got the answer Just for one column field

with Programmers in single cell (comma sepearted Prgrammers name) and Clients which happened to appear in Single cell instead appeared in individual cell …like below

ProjectNO. Prjtname Programmers Client
01 ave dee, law, amy zica

01 ave dee, law, amy rowan

Any suggestion or examples with (Stuff + For XML path) using individually with each(2 or more ) column… I had applied STuff+FOR XML path for both Programmers and Clients.

Gabriel Dias Pinto says

September 22, 2014 at 11:24 pm

Great tutorial! Thanks I had been struggling with how to do this for a while.

Tim Smith says

March 23, 2018 at 12:15 pm

Thanks, that’s been really helpful

How to combine values from multiple rows of a single column (T-SQL, Microsoft SQL Server, FOR XML PATH, CSV ).

The Problem: