0

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.

Community
  • 1
  • 1
Jorge.Methew
  • 75
  • 1
  • 10
  • So you want to get the values of `OrganizationName` and store them with comma separated into `organizations`? – Mahmoud Gamal Jul 08 '13 at 14:46
  • here is a place to start: http://stackoverflow.com/questions/5031204/does-t-sql-have-an-aggregate-function-to-concatenate-strings. I suppose you need to figure out how to concatenate values first in aggregate function. – Bulat Jul 08 '13 at 14:48
  • Yes exactly so I do not need to also create a connection to the other tables in the back end & can just manage all through items table. – Jorge.Methew Jul 08 '13 at 14:53

1 Answers1

0

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:

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • Thank you! Exactly what I was trying to achieve. – Jorge.Methew Jul 08 '13 at 15:25
  • If I may ask, is it possible to store the organizations in an hierarchic way rather than with commas in between so that I can reflect each organization as sub-level items in xml? – Jorge.Methew Jul 08 '13 at 15:59
  • @Jorge.Methew all the organizations for any item are already stored why do you want to store them in an extra column `organizations`? this is redundant data, you can make a simple `SELECT` statement to select them, there is no need to that column `organizations`. And this way you are breaking the normal forms. For the hierarchical, you can add an extra foreign key to the `organizations` table as a self key, like `ParentOrganizationId` to the same table this way each organization will have a parent organization. – Mahmoud Gamal Jul 08 '13 at 19:28