How to use STUFF Function with XML PATH and Group By Clause In SQL

There could be requirement to concatenate product names based on categories they belong. This can be done using STUFF function in combination with XML PATH and adding the GROUP BY Clause for the SELECT query to fetch Product Names by category id from Product table in our example below.

Query:

SELECT CategoryID,  
ProductName=STUFF  
(  
    (  
      SELECT DISTINCT ', '+ CAST(g.ProductName AS VARCHAR(MAX))  
      FROM Products g,Categories e   
      WHERE g.CategoryId=e.CategoryId and e.CategoryId=t1.CategoryID   
      FOR XML PATH('')  
    ),1,1,''  
)  
FROM Products t1  
GROUP BY CategoryID  

Output:

Explanation: In the above exmaple we are concatenating the Text values from multiple columns but grouping the product names by the categories each product name belongs. So here again the XMAL PATH is used with STUFF string functions and GROUP BY categoryId is used to combine the names categorywise.

In the above example the the Product names are cocatenated using the XML and product name is grouped by categories. There are 5 categories in the above example and the product names are combined by category of each product. so categorywise the concatenated product names along with category id on the left column is displayed.

When we fetch data from a table, there may be requirements to concatenate the text value of a table column in multiple rows into a single row. There are many ways we can concatenate multiple rows into single row SQL Server. We can use different ways based on need and convenience.

In this article, we will discuss how to concatenate text from multiple rows into a single text string in SQL Server using various methods which are COALESCE Function, XML PATH Function, and STUFF Function with XML PATH and Group By Clause.