I have a table that looks something like
grain 10 rice 10 bread 10 wine 20 chair 20 bed 30 desk 30
and I would like to create all possible combinations with the sum of the prices, including the unique values to create something like
grain grain grain grain 40 grain grain grain rice 40 grain grain rice rice 40 grain grain grain desk 60
what would be the quick and dirtiest way of accomplishing this?
select t1.col1, t2.col1, t3.col1, t4.col1, (t1.price + t2.price + t3.price + t4.price) from t t1 join t t2 on t1.col1 <= t2.col1 join t t3 on t2.col1 <= t3.col1 join t t4 on t3.col1 <= t4.col1;