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
|
1 2 3 4 5 6 7 |
USE AdventureWorks2012; GO 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?
|
1 2 3 4 5 6 7 8 9 10 11 |
USE AdventureWorks2012 GO 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
About Carlos Ferreira
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
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.
Great tutorial! Thanks I had been struggling with how to do this for a while.
Thanks, that’s been really helpful