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