SQL Server– Concatenate Rows using FOR XML PATH()

SQL Server– Concatenate Rows using FOR XML PATH()

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:

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

=====================================================================


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

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

=============================================================================




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.