Archive

Posts Tagged ‘path()’

TSQL – Concatenate Rows using FOR XML PATH()

27.04.2011 31 comments

This is probably one of the most frequently asked question – How to concatenate rows? And, the answer is to use XML PATH.

For example, if you have the following data:

— © 2011 – Vishal (http://SqlAndMe.com)

USE AdventureWorks2008R2

SELECT      CAT.Name AS [Category],
            SUB.Name AS [Sub Category]
FROM        Production.ProductCategory CAT
INNER JOIN  Production.ProductSubcategory SUB
            ON CAT.ProductCategoryID = SUB.ProductCategoryID

image

The desired output here is to concatenate the subcategories in a single row as:

image

We can achieve this by using FOR XML PATH(), the above query needs to be modified to concatenate the rows:

— © 2011 – Vishal (http://SqlAndMe.com)
USE AdventureWorks2008R2

SELECT      CAT.Name AS [Category],
            STUFF((    SELECT ',' + SUB.Name AS [text()]
                        — Add a comma (,) before each value
                        FROM Production.ProductSubcategory SUB
                        WHERE
                        SUB.ProductCategoryID = CAT.ProductCategoryID
                        FOR XML PATH('') — Select it as XML
                        ), 1, 1, '' )
                        — This is done to remove the first character (,)
                        — from the result
            AS [Sub Categories]
FROM  Production.ProductCategory CAT

Executing this query will generate the required concatenated values as depicted in above screen shot.

Hope This Helps!

Vishal