GROUP_CONCAT in SQL Server error

Question

Error:Column 'ReviewConsultants.ConsultantID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Query:

select 
    R.ReviewID, 
    STUFF((select distinct ',' + FirstName 
           from Users 
           where UserID = RC.ConsultantID 
           FOR XML PATH ('')), 1, 1, '') AS consultantlist
from 
    [Reviews] R, [ReviewConsultants] RC 
where 
    R.ReviewID = RC.ReviewID  
group by 
    R.ReviewID;

One review can have one or more consultants.I am trying to get the consultants for each review in a column with comma separated.

Note: names of the consultants are present in users table.

When I am trying to run the above query I am getting above error.Any help is greatly appreciated!

Answer 1

In your case you could use DISTINCT:

select DISTINCT R.ReviewID, STUFF((select distinct ','+FirstName 
                                   from Users 
                                   where UserID=RC.ConsultantID 
                                  FOR XML PATH ('')), 1, 1, '') 
                             AS consultantlist
from [Reviews] R
JOIN [ReviewConsultants] RC 
  ON R.ReviewID=RC.ReviewID;

Please avoid old join syntax.