How to use group inside stuff in sql server

Question

I have one request in which I use the stuff method. It works fine, but only in one condition repeats the same data.

Request: -

 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 repeats allgroup for both departments in the allgroup column, which is wrong. He should show only his own group for each department. I know that I must group by fulfill this request by the department, but could not complete this task.

I need to show my output for the above query, e.g.

Here he will not repeat the entire group for both departments.

Note. “This query is great for one department.”

Please try to help me to fix this request.

Answer 1

You need to associate the internal query (in the STUFF function) with the external query: you have a connection, but it does not apply to this internal SELECT statement, so the STUFF function always works on the first record, not the corresponding record. Put this after "WHERE departmentid IN (5,6)":

 AND groupID = fr.ParentGroupID 

Then you can possibly remove this JOIN completely.