Comma Separated Value in SQL Query

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.

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:

SQL-Query1.jpg

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:

SQL-Query2.jpg

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.
 

SQL-Query3.jpg

SQL-Query4.jpg

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.

SQL-Query5.jpg

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.

SQL-Query6.jpg

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

SQL-Query7.jpg

Final Result

SQL-Query8.jpg

Conclusion

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