SQLTeam.com | Weblogs | Forums

Populating one field from multiple records


#1

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.


#2

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.


#3

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


#4

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.


#5

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/