CODEWITHSUNDEEP
group-bysql-server-2014for-xml-path
Shivkumar kondi
Shivkumar kondi

Reputation: 6762

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

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.

Upvotes: 1

Views: 68

Answers (1)

marc_s
marc_s

Reputation: 754518

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

Upvotes: 1

Related Questions