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

user_6107715 0
Viewed 34

I have this schema in fiddle

enter image description here

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:

enter image description here

I am expecting this output :

enter image description here

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

for-xml-path sql-server-2014 group-by
1 Answers

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
user_133020
Related