bteraberry (4/7/2010)

---

This is a really interesting problem. I cannot think of a way to do it completely dynamic in terms of the maximum size of your combination collections and I'm hoping someone will come along with an amazing method I never would have considered.

However, if you can limit the maximum element grouping to a certain number, I have a possible suggestion for you. Basically, it's a tally table ... the numbers would join back to a ROW_NUMBER or identity or however you want to join back to your actual data. The 0's would be non-joins (so as to account for groupings smaller than the maximum.

My tiny tally table to avoid getting too many results back:

declare @test-2 table (myInt int)

insert into @test-2(myInt)

select 0

union select 1

union select 2

union select 3

union select 4

union select 5

union select 6

union select 7

union select 8

union select 9

union select 10

And my logic to build my complex-er tally table:

select t1.myInt col1,

t2.myInt col2,

t3.myInt col3,

t4.myInt col4,

t5.myInt col5,

t6.myInt as col6

from @test-2 t1

join @test-2 t2

on t2.myInt > t1.myInt

join @test-2 t3

on t3.myInt > t2.myInt or t3.myInt = 0

join @test-2 t4

on (t4.myInt > t3.myInt and t3.myInt > 0) or t4.myInt = 0

join @test-2 t5

on (t5.myInt > t4.myInt and t4.myInt > 0) or t5.myInt = 0

join @test-2 t6

on (t6.myInt > t5.myInt and t5.myInt > 0) or t6.myInt = 0

where t1.myInt > 0

order by t1.myInt, t2.myInt

With only the 10 initial values, it should return 837 possible combination of between 2 and 6 elements.

I would be interested to hear feedback.

Thanks,

-Ben