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

alt text http://img248.imageshack.us/img248/1797/updatetable.png

Up开发者_开发知识库date 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.

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