Concatenate rows with comma separated string

by Ravi Tuvar 11:42 PM 0 comments

Hey friends,

Here i am sharing a cool stuff for SQL Developers
 
Please use the following query when concatenate 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 the other 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 the distinct values with 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'

Thanks  

Ravi Tuvar – IntelliMedia Networks

Software Developer

Cell: (+91) 73837-94530

Email: ravituvar@yahoo.com