+2 votes
1 view
in SQL by (22.4k points)

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?

1 Answer

+2 votes
by (40.3k points)
edited by

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

Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...