09-29-04, 05:40 #1Registered User
- Join Date
- May 2004
Unanswered: Distinct -- but across multiple rows?
This is a simplified version of a problem I am having.
I have large number of stores. These stores get items in varying quantites. I need to work out the various combinations of items and quantities, these will be used to create boxes of items that will then get shipped to stores.
So, given that I have the following
declare @stores table( store varchar(10), item varchar(10), quantity integer ) insert into @stores select 'Store A', 'item 1', 1 union select 'Store B', 'item 1',1 union select 'Store B', 'item 2',1 union select 'Store C','item 1',1 union select 'Store C','item 2',3 union select 'Store D', 'item 1', 1 union select 'Store E', 'item 1',1 union select 'Store E', 'item 2',1
Box Item Quantity --- ---- -------- Box 1 item1 1 Box 2 item1 1 Box 2 item2 2 Box 3 item1 1 Box 3 item2 3
Store A gets Box 1 Store B gets Box 2 Store C gets Box 3 Store D gets Box 1 Store E gets Box 2
I think I may need some more levels of abstraction to get the details, but even using loops I am having trouble working out when I have seen a combination before and have a box to use or when I need to create and assign a new box.
Any thoughts/suggestions/pointers as to where on earth I start with this...