I would like to share the approach of ‘FOR XML PATH’.
We can use ‘FOR XML PATH’ to concatenate rows, if you have the database 'AdventureWorks' in SQLServer, you can run the following statement, for example:
- Concatenate rows
SELECT CAT.Name AS[Category],
STUFF((SELECT',' + SUB.Name AS [text()]
FROM Production.ProductSubcategory SUB
WHERE SUB.ProductCategoryID = CAT.ProductCategoryID
FORXML PATH('')), 1, 1, '') -- This is done to remove the first character (,) fromthe result
AS [Sub Categories]
FROM Production.ProductCategory CAT
- Get the xml data from a table
SELECT ProductCategoryKey AS '@CategoryKey',
EnglishProductCategoryName AS '@CategoryName'
FROM AdventureWorksDW2012.dbo.DimProductCategory
FOR XML PATH('Category'), ROOT('Categorys')