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
select 'Store B', 'item 1',1
select 'Store B', 'item 2',1
select 'Store C','item 1',1
select 'Store C','item 2',3
select 'Store D', 'item 1', 1
select 'Store E', 'item 1',1
select 'Store E', 'item 2',1
I need to work out that I need the following 3 different boxes, which is almost like a distinct, but spaning mulitple rows...

Box    Item   Quantity
---    ----   -------- 
Box 1  item1  1
Box 2  item1  1
Box 2  item2  2
Box 3  item1  1
Box 3  item2  3
And then I need to back track to say that

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...