SQL Server Stuff based on condition

问题:

This is my current table.

ImageID    Brand     Segment
101        Kitkat     Biscuit
101        Galaxy     Biscuit
102        Kitkat     Chocolate
102        Kitkat     Chocolate
103        Kitkat     Chocolate
103        Twix       Ice Cream

My desired table

ImageID    Brand             Segment
101        Kitkat,Galaxy     Biscuit
102        Kitkat            Chocolate
103        Kitkat, Twix      Chocolate, Ice Cream

I'm trying to group by ImageID. In such a way that if an ImageID has multiple records. Then Brand and Segment will be stuff, but not repeated [Ref: 102, Kitkat & Chocolate word didn't get repeated]

Thanks in advance.

回答1:

STUFF is a common SQL Server approach for this:

SELECT T.ImageID,
       STUFF((SELECT DISTINCT ',' + Brand
             FROM yourTable T2
             WHERE T2.ImageID= T.ImageID
             FOR XML PATH ('')),1,1,'') Brand,
       STUFF((SELECT DISTINCT ',' + Segment
             FROM yourTable T2
             WHERE T2.ImageID= T.ImageID
             FOR XML PATH ('')),1,1,'') Segment
FROM yourTable T
GROUP BY T.ImageID