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 Lisitng

Original SubCategories Listing



Into something that looks like this:

SubCategories formatted data

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

The results look like the following:

XMLResult

 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:

CSV Result

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:

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

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

What does the final solution look like?

SubCategories formatted data
SubCategories formatted data

 References

 

Summary
Article Name
How to combine values from multiple rows of a single column
Author
Description
Describes how to in a t-sql query to roll-up multiple rows into one row and one column.

Creative Commons License
How to combine values from multiple rows of a single column (T-SQL, Microsoft SQL Server, FOR XML PATH, CSV ). by Carlos Ferreira, unless otherwise expressly stated, is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.

About Carlos Ferreira

Comments

  1. 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

  2. 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.

  3. Gabriel Dias Pinto says:

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

Leave a Reply

%d bloggers like this: