Comma Separated Value in SQL Query

Jignesh Trivedi

3y149.5k5100Article

SQL ServerComma Separated ValueSQL Comma Separated Value

5 Comments

Join the conversation! Your thoughts help the community grow.

Sachin Patel3y ago
We may use STRING_AGG for SQL 2017+ - SQL Fiddle for this example - http://www.sqlfiddle.com/#!18/7d09de/9
Jignesh Trivedi13y ago
COALESCE is nothing but the version of ISNULL, which can take more than two parameters. For XML Path is one of the techniques for find out comma separated value. Same thing we can achieve by using recursive CTEs, SQL CLR function, recursive UDFs, and cursor based approach etc.
Anil Kumar13y ago
Good work Jignesh, but I would like to use COALESCE with help of a variable instead this approach. That one will be more better than this in understanding point to efficiency.
Jignesh Trivedi13y ago
Thanks for commenting, Yes you are right, if the article contain text (SQL Syntax), than it more beneficial to reader. My original article document is containing SQL syntax, not image. But I don’t know how it converted from text to image.
Former member13y ago
Nice article Jingesh . I wonder if you can put SQL Snippet instead of images then reader may be more benefited. /DJ

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.

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.