Comma Separated Value in SQL Query

Introduction

I have reviewed and answered many articles related to SQL Server. For the previous one and a half months, I found 3 to 4 articles related to finding comma separated values in SQL Server. So I decided to write an article on Comma Separated Values in SQL, it might help people who are looking for a solution of this.

Problem statement

I have three tables called Employees, ItemMaster and OrderMaster Relation, among these three tables are as in the following:

Now I want a report be employee that shows employee VS items in the comma separated value data.

The example result view is as in the following:

Solution

As per prerequisites, I have required that the three tables Employees, ItemMaster and OrderMaster, so I have created thess tables and inserted some dummy data into them.
 

Now the first question is how to get the comma separated values, the answer is that using "FOR XML PATH" we can determine the comma separated values from n number of rows for a single column.

Now for the perfect comma separated value we must remove the first comma in the above query result. This can be done using "STUFF" or the SUBSTRING function. STUFF deletes a specified length of characters from the given string and inserts another set of characters at a specified starting point.

The problem with using SUBSTRING is that we only must pass a fixed length (that specifies how many characters of the expression will be returned). In the above example, it is 200000.

Final Query

Final Result

Conclusion

Using the SQL functiond "FOR XML PATH", "STUFF" and "SUBSTRING", we can get comma separated values in SQL.