SQL Server Stuff based on condition

Question

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.

https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 sound right? Group_concat is the usual term for this, I beleive you need to fake it in MSSQL — Twelfth, Mar 13 '18 at 19:44
In record 103. I have kitkat brand & twix. In segment i have choclate & ice cream. So basically i want to stuff both of the values — MDMalik, Mar 13 '18 at 19:44
@Twelfth let me check, but seems ur guiding me to the right path let me check that also. thanks — MDMalik, Mar 13 '18 at 19:45
https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server might be better, uses the stuff function. Googling MSSQL 2008 group_concat gives tons of info. Apparently 2017 has a better function for it. — Twelfth, Mar 13 '18 at 19:47

score 5 · Accepted Answer · answered Mar 13 '18 at 19:53

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

SQL Server Stuff based on condition

1 Answers1

Linked