Post
Ask Question

Select Column Values as Comma Separated in SQL Server using COALESCE

Comma Separated values from table in Sql Server using COALESCE function:
 
It Returns the Id of All Products based on the Category. The returned Products Ids are Separated by Comma using SQL Server. 
  1. Create Procedure GetProductByCategory  
  2. @Category nvarchar(50) ,  
  3. @ProductIds Varchar(100)   
  4.    
  5. As  
  6. Begin  
  7.    
  8. Select @ProductsIds = Coalese(@ProductIds + ',' , ' ') + Cast(ProductId as Varchar(10)) from Product  
  9. Where Category=@category  
  10.    
  11. End   
Execute the Store Procedure: 
  1. Declare @ProductIds Varchar(100)  
  2. Exec GetProductByCategory 'Books' , @ProductIds  
  3. Select @ProductIds