We can do this in many ways, let’s discuss few of them.
Considering the below Employee table
1. STUFF and FOR XML PATH(‘’)
we can get comma separated string as sql server results with STUFF and FOR XML PATH(‘’) as below
SELECT STUFF(( select ','+ Name from Employee FOR XML PATH('') ) ,1,1,'') AS Names
In the above query
select ','+ Name from Employee FOR XML PATH('')
returns the Employee names prefixed with comma like ,Venkat,Anil,Mounika,Chandra,Raj,Dileep,Shyam
So to remove the prefixed comma we are using STUFF.
Below is the STUFF syntax
STUFF(string, start, length, new_string)
|string||This is Required field and The string to be stuffed or modified|
|start||This is Required field and The start position of a string to be deleted|
|length||This is Required field and The number of characters to delete from string|
|new_string||This is Required field and The new string to insert into string at the start position|