Create a comma separated list from column using select statement

In this article, I will clarify two techniques with a model, how to utilize the SQL Server COALESCE work and another is STUFF work in select explanation.

Here we go:


The COALESCE capacity restores the first non-invalid articulation in a list.If there is NULL Value in your string you will have your outcome set as NULL.


STUFF places one string within another string. stuff capacity deals with “strings” (burn, nchar, varchar, nvarchar).

at point one, for a length of 1 – fundamentally its applying first comma, so you get the rundown of information as you wanted.And the inner FOR xml path(”) chooses the rundown of information for each Row, and make every datum with a ‘,’ so you get something like-(A, B).

Assume we have following information in Employee table and we have to make a semicolon isolated rundown of ‘EmpID’ and ‘Emp_Name’ at that point we can utilize strategies as appeared in underneath.

 CREATE TABLE [dbo].[EmpDetail](    
    [EmpId] [int] IDENTITY(1,1) NOT NULL,    
    [Emp_Name] [varchar](15) NULL,    
    [Empsalary] [int] NULL,    
    [EmpId] ASC    
SELECT * FROM [EmpDetail]  
EmpId       Emp_Name        Empsalary  
----------- --------------- -----------  
1           ram             10000  
2           ramesh          11000  
3           manish          12000  
4           Shivom          15000  
(4 row(s) affected)  

We can accomplish the outcome utilizing COALESCE work. Given beneath is a model.

 --Method 1:  
    @EmpName VARCHAR(30);  
SELECT  @EmpID = COALESCE(@EmpID + ',', '') + CONVERT(VARCHAR(50), EmpId) ,  
        @EmpName = COALESCE(@EmpName + ',', '') + Emp_Name  
FROM    [EmpDetail];  
        @EmpName AS EmpName;  
EmpID      EmpName  
---------- ------------------------------  
1,2,3,4    ram,ramesh,manish,Shivom  
(1 row(s) affected)  

We can likewise accomplish a similar utilizing STUFF work. Given underneath is a model.

 --Method 2:  
FROM   EmpDetail  
FOR XML path('')), 1, 1,'') EmpID,  
        STUFF((SELECT','+ Emp_Name  
        FROM   EmpDetail  
        FOR XML path('')), 1, 1,'') EmpName  
EmpID      EmpName  
---------- ------------------------------  
1,2,3,4    ram,ramesh,manish,Shivom  
(1 row(s) affected)  

The result of all of the above methods are the same. Given above is the result.

I hope this article will be useful and resolve your issues. If you are fed up using COALESCE function so here is another approach by using STUFF function. I hope you will visit my blog again.

Leave a Reply

Your email address will not be published. Required fields are marked *