Concatenate rows with comma separated string

Please use the following query when concatenating multiple rows with a single comma separated string (row):

DECLARE @iXml xml;
SELECT @iXml = (
  SELECT ProductName + ','
  FROM Northwind.dbo.Products
  FOR XML PATH);

SELECT @iXml.value('.','nvarchar(max)');

Or another way is:

SELECT STUFF((SELECT ',' + RTRIM(ProductName ) FROM Products FOR XML PATH('')),1,1,'') AS 'Products'

Now this block will return a string with comma separated rows..

If you want to get distinct values with a comma separated row.. then do this:

DECLARE @iXml xml;
 
SELECT @iXml = (
  SELECT distinct ProductName + ','
  FROM Northwind.dbo.Products
  FOR XML PATH);

SELECT @iXml.value('.','nvarchar(max)');

or:

SELECT STUFF((SELECT Distinct ',' + RTRIM(ProductName ) 
    FROM Products FOR XML PATH('')),1,1,'') AS 'Products'

This would be useful when you have to check between conditions in a SQL query.

Votes are welcome..!