Генерация комбинаций данных для столбца динамически на основе SQL другого столбца

У меня есть таблица, как показано ниже -

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

Мне нужно сгенерировать все возможные комбинации вместе с uniqueID для набора уникальных значений в COL1, показанном ниже - Например, в COL1 есть 3 уникальных значения, возможны 6 комбинаций, поэтому в результате должно быть 18 строк. Может быть n уникальных значений. Мне нужно динамическое решение, которое должно работать с любым количеством комбинаций и значений.

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

Пожалуйста, помогите и предложите ответ. Я пробовал использовать LAG, LEAD, CROSS JOIN, но не смог найти решение.

Ответом может быть любой из сценариев HANA SQL Script, Oracle SQL или MS-SQL.


sql sql-server window-functions hana-sql-script
person Gowthi    schedule 16.12.2020    source источник
comment
Покажи нам, что ты пробовал.   -  person Dale K    schedule 16.12.2020

Ответы (1)


arrow_upward
0
arrow_downward

Я придумал следующее решение, основанное на рекурсивном CTE, оконных функциях и арифметике.

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);

Вывод:

+----+------+------+
| 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    |
+----+------+------+

Попробуйте на rextester.com для Microsoft SQL Server, Oracle и PostgreSQL.

person Andrei Odegov    schedule 17.12.2020