Generate data combinations for a column dynamically based on another column SQL

Question

I have a table like below -

COL1    COL2
-------------
101     A
102     B
102     C
102     D
103     C
103     E

I need to generate all possible combinations along with uniqueID for a set of unique values in COL1 shown as below - For example - There are 3 unique values in COL1, 6 combinations are possible and so 18 rows should be in the result. There can be "n" number of unique values. I need a dynamic solution that should work with any number of combinations and values

1,101,A
1,102,B
1,103,C
2,101,A
2,102,B
2,103,E
3,101,A
3,102,C
3,103,C
4,101,A
4,102,C
4,103,E
5,101,A
5,102,D
5,103,C
6,101,A
6,102,D
6,103,E

Please help and suggest an answer. I tried using LAG,LEAD, CROSS JOIN, unable to get to a solution.

Answer can be using any of HANA SQL Script or Oracle SQL or MS-SQL.

Answer 1

declare @t table (col1 int, col2 varchar(5));
insert into @t(col1, col2)
values
(101, 'A'), (101, 'B'), (101, 'C'), (101, 'D'),
(102, 'E'), (102, 'F'), (102, 'G'),
(103, 'H'), (103, 'I'), 
(104, 'J'), (104, 'K');


select t.combid, o.col1, o.col2
from 
(
    --number of combinations
    select agr.col1, agr.col2cnt, 
        --float-->decimal:truncate --> int
        cast(cast(exp(sum(log(agr.col2cnt)) over(order by agr.col1 desc)) as decimal(38,0)) as int)  as restcombs,     
        cast(cast(exp(sum(log(agr.col2cnt)) over()) as decimal(38,0)) as int)  as combsint
    from
    (
    --count of col2 per col1
    select col1, count(*) as col2cnt
    from @t
    group by col1
    ) as agr
) as dc
cross apply
(

    select jt.combid,
        1 + (1+(jt.combid-1)/(dc.restcombs/dc.col2cnt)-1)%dc.col2cnt  as col2ordinal --ordinal of col2 within the combination            
    from
    (
    --just a tally, from 1 till combinations:=combsint
    select top (dc.combsint) row_number() over(order by @@spid) as combid
    from sys.all_objects as a
    cross join sys.all_objects as b
    cross join sys.all_objects as c 
    ) as jt
) as t
join
(
    --ordinal of col2 per col1
    select col1, col2, row_number() over(partition by col1 order by col2) as col2ordinal
    from @t
) as o on dc.col1 = o.col1 and t.col2ordinal = o.col2ordinal
order by t.combid, o.col1;

Answer 2

I came up with the following solution based on recursive CTE, window functions and arithmetic.

with
  a as (
    select 101 as col1, 'A' as col2
    union all select 102, 'B'
    union all select 102, 'C'
    union all select 102, 'D'
    union all select 103, 'C'
    union all select 103, 'E'
  ),
  b as (
    select
      col1, col2,
      count(*) over() as ct,
      count(*) over(partition by col1) as cc1,
      dense_rank() over(order by col1 desc) as rk1,
      row_number() over(partition by col1
                        order by col2) as rn12
    from a
  ),
  r as (
    select
      col1, col2, ct / cc1 as rq, ct / cc1 as ll, cc1, rk1, rn12
    from b
    union all
    select col1, col2, rq, ll - 1, cc1, rk1, rn12
    from r
    where ll > 1
  )
select
  iif(rk1 = 1, (ll - 1) * cc1 + rn12, (rn12 - 1) * rq + ll) as id,
  col1, col2
from r
order by id, col1, col2
option (maxrecursion 0);

Output:

+----+------+------+
| id | col1 | col2 |
+----+------+------+
|  1 |  101 | A    |
|  1 |  102 | B    |
|  1 |  103 | C    |
|  2 |  101 | A    |
|  2 |  102 | B    |
|  2 |  103 | E    |
|  3 |  101 | A    |
|  3 |  102 | C    |
|  3 |  103 | C    |
|  4 |  101 | A    |
|  4 |  102 | C    |
|  4 |  103 | E    |
|  5 |  101 | A    |
|  5 |  102 | D    |
|  5 |  103 | C    |
|  6 |  101 | A    |
|  6 |  102 | D    |
|  6 |  103 | E    |
+----+------+------+

Try it at rextester.com for Microsoft SQL Server, Oracle and PostgreSQL.