generate-data-combinations-for-a-column-dynamically-based-on-another-column-sql-oracle, Oracle

generate-data-combinations-for-a-column-dynamically-based-on-another-column-sql-oracle

--Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
--please drop objects you've created at the end of the script 
--or check for their existance before creating
--'\\' is a delimiter

with
  a as (
    select 101 as col1, 'A' as col2 from dual
    union all select 102, 'B' from dual
    union all select 102, 'C' from dual
    union all select 102, 'D' from dual
    union all select 103, 'C' from dual
    union all select 103, 'E' from dual
 --   union all select 101, 'Z' from dual
  ),
  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(col1, col2, rq, ll, cc1, rk1, rn12) 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
  decode(rk1, 1, (ll - 1) * cc1 + rn12, (rn12 - 1) * rq + ll) as id,
  col1, col2
from r
order by id, col1, col2


run \| edit \| history \| help	0