SQL get all possible combination of numbers

Question

i have a table like this

 | Number |
 ----------
 | 1      |
 | 2      |
 | 4      |
 | 8      |
 | 16     |
 | 32     |

can create a function that will return that

| 1 | 2 | 3 | 4 | 5  | 6  | sum |
 --------------------------------
| 1 |   |   |   |    |    | 1   |
| 1 | 2 |   |   |    |    | 3   |
| 1 | 2 | 4 |   |    |    | 7   |
| 1 | 2 | 4 | 8 |    |    | 15  |
| 1 | 2 | 4 | 8 | 16 |    | 31  |
| 1 | 2 | 4 | 8 | 16 | 32 | 63  |

Your example does not show "All possible combinations". For example, is 1 + 4 + 32 included or not? — gaborsch, Jan 24 '19 at 15:23
Are you trying to get a running total? What SQL dialect are you using? Questions should include a tag for the DBMS implementation (e.g. MySQL, PostgreSQL, Oracle, MS SQL Server, IBM DB2, etc.) being used — rsjaffe, Jan 24 '19 at 15:25
I would change your title as your desired output does not require "all possible combinations". "1" for example is in all combinations you require, "2" is in all bar one. For "all possible" they wouldn't be. e.g. 4 + 32 is a possible combination, as is 2+8, etc. — ChrisCarroll, Jan 24 '19 at 15:26
What are you trying to do with this? I can't think of a scenario where this would make sense to produce/use this information in this way. — UnhandledExcepSean, Jan 24 '19 at 15:36
This feels like an attempt at converting integers to binary and back again (perhaps to set up for a bitmask). There may be functions built into whatever RDBMS you are using to help with this without getting into complicated UDFs or pivots. Please explain more about what you are doing here (or why) and what RDBMS you are using. — JNevill, Jan 24 '19 at 15:43

score 1 · Answer 1 · answered Jan 24 '19 at 15:40

An interesting request, here is a solution for MSSQL. We can provide better solutions if you explain your main purpose more clearly.

create table #temp (num int)
insert into #temp (num) values (1),(2),(4),(8),(16),(32)

select num, rn = ROW_NUMBER() OVER (order by num asc)
into #temp2
from #temp

select num,num2=null,num3=null,num4=null,num5=null,num6=null,Total=(select sum(num) from #temp2 where rn in (1)) from #temp2 where rn = 1
union
select null,num,null,null,null,null,Total=(select sum(num) from #temp2 where rn in (1,2)) from #temp2 where rn = 2
union
select null,null,num,null,null,null,Total=(select sum(num) from #temp2 where rn in (1,2,3)) from #temp2 where rn = 3
union
select null,null,null,num,null,null,Total=(select sum(num) from #temp2 where rn in (1,2,3,4)) from #temp2 where rn = 4
union
select null,null,null,null,num,null,Total=(select sum(num) from #temp2 where rn in (1,2,3,4,5)) from #temp2 where rn = 5
union
select null,null,null,null,null,num,Total=(select sum(num) from #temp2 where rn in (1,2,3,4,5,6)) from #temp2 where rn = 6
order by Total asc

Not exactly the same,yes, but produces the logic. I think his purpose is different in real, that's why it is not clearly explained here. I thought this query will help him to struct his own query. @UnhandledExcepSean — Eray Balkanli, Jan 24 '19 at 15:57

Seaport · Answer 2 · 2019-01-24T15:45:59.880

0

Is the number of returned columns dynamic? If yes, then I do not think it can be done via a SQL function.

If you just look for the last column - "sum", you may use the code below.

SELECT power(2,RowID) -1

RowID is the sequential row number.

edited Jan 24 '19 at 15:45

answered Jan 24 '19 at 15:30

Seaport

153
2
14

score -1 · Answer 3 · answered Jan 24 '19 at 16:11

Here is how I would approach the problem. I intially thought you could use an apply operator however the union approach is the easiest to work with. The main difference between my answer and Eray's (kudos I really liked the approach) is that I'm using uncorrelated sub queries in line the generate the sum values and to maintain the unique number going down each of the columns.

I would argue that this COULD be achieved using dynamic SQL for an unknown number of columns but I don't have the time or resource to try and work that out now. Please see below code, I hope it helps.

declare @numbers table
(
    numberid int
);

insert into @numbers (numberid)
values
(1),
(2),
(4),
(6),
(8),
(16),
(32);


select numberid as [1], null as [2], null as [3], null as [4], null as [5], null as 
[6], (select sum(numberid) from @numbers where numberid=1) as [sum] from @numbers 
where numberid =1
union
select (select numberid from @numbers where numberid=1), numberid, null, null, null, 
null, (select sum(numberid) from @numbers where numberid in (1, 2)) from @numbers 
where numberid =2
union
select (select numberid from @numbers where numberid=1), (select numberid from 
@numbers where numberid=2), numberid, null, null, null, (select sum(numberid) from 
@numbers where numberid in (1, 2, 4)) from @numbers where numberid =4
union
select (select numberid from @numbers where numberid=1), (select numberid from 
@numbers where numberid=2), (select numberid from @numbers where numberid=4), 
numberid, null, null, (select sum(numberid) from @numbers where numberid in (1, 2, 4, 
8)) from @numbers where numberid =8
union
select (select numberid from @numbers where numberid=1), (select numberid from 
@numbers where numberid=2), (select numberid from @numbers where numberid=4), (select 
numberid from @numbers where numberid=8), numberid, null, (select sum(numberid) from 
@numbers where numberid in (1, 2, 4, 8, 16)) from @numbers where numberid =16
union
select (select numberid from @numbers where numberid=1), (select numberid from 
@numbers where numberid=2), (select numberid from @numbers where numberid=4), (select 
numberid from @numbers where numberid=8), (select numberid from @numbers where 
numberid=16), numberid, (select sum(numberid) from @numbers where numberid in (1, 2, 
4, 8, 16, 32)) from @numbers where numberid =32;

SQL get all possible combination of numbers

3 Answers3