How to use group by inside Stuff in Sql Server

Question

I have one query in which i am using stuff method. It works fine but only in one condition in repeats the same data.

Query :-

select fr.repairid, fr.repairName, fr.labourMins, fr.sortOrder,
    stuff(
      (
         select ' ---> ' + groupname,departmentid
         from tblRepairGroup
         where departmentid in(5,6) 
         for XML PATH(''),TYPE
      ).value('.','NVARCHAR(MAX)'),
      1, 5, ''
    ) as allgroup
from tblFlatRateRepair fr 
inner join 
    tblRepairGroup g 
    on fr.parentGroupID = g.groupID 
where fr.repairid in (2,4);

OutPut :-

It just repeat the allgroup for both department in allgroup column which is not right.It must show only it's own Group per department. I know i have to group by this query by departmentid but not able to complete that task.

I need to show my output for above query like

Here it is not gonna repeat the allgroup for both department.

Note :- This query works fine for single department.

Please try to help me for correct this query..

Answer 1

You need to link the inner query (in the STUFF function) to the outer query: you have a join, but it isn't applying to that inner SELECT statement, so the STUFF function is always operating on the first record it find rather than the related record. Put this after "WHERE departmentid IN(5,6)":

AND groupID = fr.ParentGroupID

Then you can probably remove that JOIN entirely.