Thursday, 12 December 2013

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
                        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!

No comments:

Post a Comment

Total Pageviews