Back
Consider a database table holding names, with three rows:
PeterPaulMary
Peter
Paul
Mary
Is there an easy way to turn this into a single string of Peter, Paul, Mary?
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
Sam
Dalia
MN
Moly
Edward
Result:
DepartmentID
Jashmine, Sam, Dalia
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
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
SELECT STRING_AGG( ISNULL(EmployeeName, ' '), ',') As Countries
From FROM dbo.Employees EM
31k questions
32.8k answers
501 comments
693 users