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