How to concatenate text from multiple rows into a single text string in SQL server?

Question

Consider a database table holding names, with three rows:

Peter

Paul

Mary

Is there an easy way to turn this into a single string of Peter, Paul, Mary?

Answer 1

In SQL 2005, if you are trying to join two tables with one-to-many relationships use XML PATH method to concatenate the rows:

Are you interested in learning SQL from scratch! Have a look at this interesting video on SQL provided by Intellipaat:

 Table: Employees 

DepatmentID	EmployeeName
OP	Jashmine
OP	Sam
OP	Dalia
MN	Moly
MN	Edward

Result:

DepartmentID	EmployeeName
OP	Jashmine, Sam, Dalia
MN	Moly, Edward

 Use this code:

SELECT DISTINCT EM2.DepartmentID,     SUBSTRING((SELECT ','+EM1.EmployeeName AS [text()]            FROM dbo.Empolyees EM1            WHERE EM1.EmployeeID = EM2.EmployeeID            ORDER BY EM1.DepartmentID            FOR XML PATH ('')        ), 2, 1000) [Employees]FROM dbo.Employees EM2 

• You can also use STRING_AGG to do the same in this way:

SELECT STRING_AGG( ISNULL(EmployeeName, ' '), ',') As Countries

       From FROM dbo.Employees EM