How to group by in FOR XML clause in SQL Server 2014?

Question

I have this schema in fiddle

My code:

SELECT  
    MUID, weekcounter,
    STUFF((SELECT  ',' + Category
           FROM tb EE
           WHERE EE.MUID = E.MUID AND Ranknum <= 3
           FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') AS listStr
FROM tb E
GROUP BY E.MUID, E.weekcounter

I am getting wrong output like this:

I am expecting this output :

I don't have option to use string_aggr() in SQL Server 2014.

Answer 1

I believe if you want to get the desired output, you'd have to use the two columns you want to group by in the correlated subquery (in the STUFF part), too.

Try this code:

SELECT  
    MUID, weekcounter,
    STUFF((SELECT  ',' + Category
           FROM tb EE
           WHERE EE.MUID = E.MUID 
             AND EE.weekcounter = E.weekcounter
             AND Ranknum <= 3
           FOR XML PATH, TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') AS listStr
FROM 
    tb E
GROUP BY 
    E.MUID, E.weekcounter