Post
Ask Question

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, which might help people who are looking for a solution to this.

Problem statement

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

SQL-Query1.jpg

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

The example result view is as in the following.

Employee Name Items
Jignesh key Board, Mouse, Processor
Tejas Monitor, Processor, Processor
Rakesh key Board
Ganesh key Board, Processor

Solution

 I have needed three tables, Employees, ItemMaster, and OrderMaster, so I have made these tables as temporary table and put some dummy data in them by using below SQL queries.

CREATE TABLE #Employees
(
	EmployeeId int,
	EmployeeName varchar(50)
)

The SQL query above creates a temporary database record.

Use the below SQL insert query to insert Data into a temporary table.

INSERT INTO #Employees Values (1,'Jignesh'),
(2,'Tejas'),
(3,'Rakesh'),
(4,'Ganesh')

Create another temporary table named as ItemMaster by using below SQL query.

CREATE TABLE #ItemMaster
(
	ItemId int,
	ItemName varchar(50)
)

Now use the SQL query below to insert the data into the ItemMaster table.

INSERT INTO #ItemMaster VALUES (1,'key Board'),
(2,'Mouse'),
(3,'Monitor'),
(4, 'Processor')

Create another temporary table named as OrderMaster by using the following SQL statement.

CREATE TABLE #OrderMaster
(
	OrderId int,
	EmployeeId int,
	ItemId int
)

Now use the SQL query below to insert the data into the OrderMaster table.

INSERT INTO #OrderMaster VALUES
(1,1,1),
(1,1,2),
(2,2,4),
(2,2,3),
(2,2,4),
(3,3,1),
(3,4,1),
(3,4,4),
(3,1,4)

Now check the data in three tables by using below SQL query.

select * from #Employees;
select * from #ItemMaster;
select * from #OrderMaster;

Below SQL statement is selecting three columns: EmployeeId, EmployeeName, and ItemName from three tables: #OrderMaster, #Employees, and #ItemMaster. The #OrderMaster table and the #Employees table are being joined on the EmployeeId column, and the #ItemMaster table is being joined on the ItemId column. The resulting rows are then ordered by EmployeeName.

SELECT E.EmployeeId,EmployeeName,ItemName FROM #OrderMaster O
JOIN #Employees E on E.EmployeeId = O.EmployeeId
JOIN #ItemMaster I on I.ItemId=O.ItemId
ORDER by E.EmployeeName

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.

SELECT ',' + ItemName from #ItemMaster
for XML PATH('')

Final Query

SELECT e.EmployeeId,e.EmployeeName, (SELECT SUBSTRING (
(SELECT ','+ItemName FROM #OrderMaster O join #ItemMaster i 
ON i.itemid = o.itemid WHERE o.employeeid=e.employeeid FOR XML PATH ('')),2,2000))AS items FROM #Ordermaster o
join #Employees e ON e.employeeid = o.employeeid GROUP BY e.employeeid,e.employeename

Final Result

Conclusion

Using the SQL functions “FOR XML PATH”, “STUFF” and “SUBSTRING”, we can get comma-separated values in SQL Server.


Similar Articles