Q:

Update a column based on results from junction table

Possible duplicate: Concatenate row values T-SQL
I have three tables.  Items, Organizations & Items_Organizations junction table which is providing the many to many relationship between the two others. 
Items table includes the column 'organizations' where I want to store the values I receive from the junction table for each item; combining each organization with a comma or similar. 
As far as I read it is not the best practice to store multiple values in a column, however I need to display organizations for each item in front-end through some handler & did not come up with a better idea than storing multiple values in one column. 
So what I am trying to do in the back end is to update the 'organizations' column information using something like that;
Receiving the organizations for a specific item:
SELECT OrganizationName FROM organizations
JOIN organizations ON organizations.organizationID =
Items_Organizations.organizationID 
WHERE Items_Organizations.item_ID = '1'

Trying to update the column 'organizations' using the results table of the query above using UPDATE
UPDATE items SET organizations = ? 

It is quite difficult to formulate the question.  I hope I made it clear enough though. 

A:

Try this:
UPDATE i
SET i.organizations = 
  STUFF((
    SELECT ', ' + o.OrganizationName
    FROM Items_Organizations AS io
    INNER JOIN organizations AS o  ON o.organizationID = io.organizationID
    WHERE io. Item_Id = i.Item_Id AND io. Item_Id = 1
    FOR XML PATH(''))
  ,1,2,'')
FROM items AS i
WHERE i.Item_id = 1;

See it in action here:

SQL Fiddle Demo

