Populating one field from multiple records

I have a scenario that I cant figure out.

I have a sold at table that I need to populate with an item number and all the locations this item sold at. So I want the item sold at field to be a string field of some length say 100 characters. I would go through my sales detail table and if I find a record where an item is sold I would right the location number into the Sold At field. But as I read through the sales detail table and I find another instance where this item has been sold I want to skip if this location is already in that Sold At field but if not I want to add a space and then the next location found.

IE the fields would look like this if 4 locations sold this item 101 102 316 433

Would there be a way to accomplish this using SQL?

For the final report that the sold at table is used in I want to display this Sold At field as shown above.

Don't use a single string. Use a separate table with a column for each location. That will make is so much easier to process that data.

maybe research normalization, and then maybe try to get to form 2or3...or maybe FOR XML?

But eventually for display I need it to look like : 101 103 311 316 in one column. I would still need to go through the table you suggest creating anyway. The report I am trying to get to is a slow sales report by location and the end user wants to see where this item did sell to decide if the product should then be moved to another location.

Since this is for output only - and the database is already normalized - take a look at using FOR XML to build a list of items to be displayed in a single column.

Here is a simple outline of how to do this: https://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/