Update column using rows of a table, without using cursor or any other iteration.

Question

Update Combined column using CategoryCode of every OrderId. In this example there are two OrderIds 990 and 986. Need to concatenate categories of these two individually.

The desired result is like this.

990 Bus, Pub, Shoot, Club, Bus, Hos

Thanks.

Answer 1

UPDATE test
SET    combined = Left(j.combined, Len(j.combined) - 1)
FROM   test t
   JOIN (SELECT a.orderid,
                (SELECT categorycode + ','
                 FROM   test b
                 WHERE  b.orderid = a.orderid
                 ORDER  BY orderid
                 FOR XML PATH('')) AS combined
         FROM   test a
         GROUP  BY orderid) AS j
     ON j.orderid = t.orderid