T-SQL :: use GROUP BY CUBE to generate all possible combinations

Question

In a post I found that I can use GROUP BY CUBE to find all possible combinations:

select concat(a,b,c,d)
from (select 'a','b','c','d') as t(a,b,c,d)
group by cube(a,b,c,d)
having len(concat(a,b,c,d)) = 3

The code is pretty because is very easy to understand. I would like to use the same code but with int instead of char. Basically I would like to find all possible combinations of numbers (1,2,3,4).

The goal is to sum them and generate all possible totals:

1 + 2 + 3 = 6
2 + 3 + 4 = 7
3 + 4 + 1 = 8
etc..

I'm trying to resolve a knapsack problem in T-SQL and I would like to see if GROUP BY CUBE can be a solution

score 3 · Accepted Answer · answered Oct 12 '20 at 13:12

You need to take NULLs into account more explicitly, but something like this:

select coalesce(a, 0) + coalesce(b, 0) + coalesce(c, 0) + coalesce(d, 0)
from (values (1, 2, 3, 4)) t(a,b,c,d)
group by cube(a,b,c,d)
having (case when a is not null then 1 else 0 end +
        case when b is not null then 1 else 0 end +
        case when c is not null then 1 else 0 end +
        case when d is not null then 1 else 0 end
       ) = 3;

Here is a db<>fiddle.

I should note that an alternative method of doing this uses explicit joins:

with t as (
      select t.*
      from (values (1), (2), (3), (4)) t(x)
     )
select t1.x + t2.x + t3.x
from t t1 join
     t t2
     on t1.x < t2.x join
     t t3
     on t2.x < t3.x;

These are not exactly the same if the values can be repeated. However, you might find that the join version is faster on larger amounts of data.

Is there a way to put a limit like "select only values lower than 8"? — Francesco Mantovani, Oct 12 '20 at 13:21
@FrancescoMantovani . . . You would add a `having` clause: `coalesce(a, 0) + coalesce(b, 0) + coalesce(c, 0) + coalesce(d, 0) < 8`. — Gordon Linoff, Oct 12 '20 at 13:24

T-SQL :: use GROUP BY CUBE to generate all possible combinations

1 Answers1