Concatenate rows with comma separated string

by 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

ravi tuwar

Developer

Cras justo odio, dapibus ac facilisis in, egestas eget quam. Curabitur blandit tempus porttitor. Vivamus sagittis lacus vel augue laoreet rutrum faucibus dolor auctor.

0 comments:

Post a Comment