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