How to select multiple columns but only group by one?
In SQL Server you can only select columns that are part of the GROUP BY clause, or aggregate functions on any of the other columns. I've blogged about this in detail here. So you have two options:


Add the additional columns to the GROUP BY clause:

GROUP BY Rls.RoleName, Pro.[FirstName], Pro.[LastName]

Add some aggregate function on the relevant columns:

SELECT Rls.RoleName, MAX(Pro.[FirstName]), MAX(Pro.[LastName])



The second solution is mostly a workaround and an indication that you should fix something more general with your query.