I've got a parent and child table. When I run the query it returns a line for each record in the Child table. What I need to do is to concatenate the child records to only return one line for each record in the parent table. I could write a function in ASP and pass the parent ID, but I want to do this in a stored procedure or SQL query, but have no idea where to start.

SELECT p.P_ID, p.P_Name, m.LName + ', ' + m.Fname EmpName, m.Role
FROM ProjectMgrs AS m RIGHT JOIN Projects AS p ON m.ProjID = p.P_ID
WHERE (p.ProjID=41)
and (m.Role='M');

Thanks in advance