Concatenate Rows using FOR XML PATH() IN SQL

'Concatenate Rows using FOR XML PATH() IN SQL


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.

We can also use  FOR XML to convert out resulting table in to xml format.there are some other variants of 'FOR XML'.Try it...

Hope This Helps!