Como selecionar todas as combinações de pares PersonID, TimetableID de uma tabela de horários

with tbltmp(x, y) as (
    select * from (
        VALUES 
            (5215, 57), 
            (18943, 221), 
            (18943, 230), 
            (18943, 238), 
            (21488, 257), 
            (21488, 270), 
            (5215, 67), 
            (5215, 77), 
            (5215, 87), 
            (5215, 97)  
    ) as t(x, y)
),
rtbltmp as (
    select d.*, 
        dense_rank() over(order by x) as rnx,
        row_number() over(partition by x order by y) as rny,
        count(y) over(partition by x) as  cnt 
    from tbltmp d
)
, solution(x,y, rnx, rny, js) as (
    select x,y, rnx, rny, cast(concat('[' , x , ',' , y , ']') as varchar(max))
    from rtbltmp
    where rnx = 1 
    
    union all
    
    select d.x, d.y, d.rnx, d.rny, 
        concat(r.js, ',[',  d.x,  ',', d.y, ']')
    from solution r
    join rtbltmp d
        on 
            (d.rnx = r.rnx + 1)
),
jsolution(grp, js) as (
    select row_number() over(order by rnx), concat('[', js, ']') as js from solution
    where rnx = (select count(distinct x) from rtbltmp)
)
select * from jsolution ;

https://dbfiddle.uk/PLiK04hT

Cabe a você converter as matrizes JSON em linhas usando a sintaxe do SQLServer.

(Versão ORACLE como exemplo: https://dbfiddle.uk/6VGG4Uad )

Podemos considerar essa tarefa como fazer combinações únicas dos membros de um conjunto - cada combinação deve ter 1 membro de cada conjunto.

Com esses dados de teste temos 3 conjuntos

set 5215(57, 67, 77, 87 97) - 5 members 
set 18943(221, 230, 238) - 3 members
set 21488(257, 270) - 2 members

Então a contagem de combinações é 5*3*2=30 - totOver.
Podemos multiplicar (gerar séries) cada linha para (totOver/rQty) linhas.
Por exemplo

(5215,57) to (5215,57,1),(5215,57,2)...(5215,57,6)
(5215,67) to (5215,67,7),(5215,67,8)...(5215,67,12)
...
(5215,97) to (5215,97,25),(5215,97,26)...(5215,97,30)

O último número no triplo é o número do grupo.

Veja o exemplo

PrepRnsaída

with  PersonCounts as(
   select PersonId,count(*)xCnt,row_number()over(order by count(*))grn
   from tblTimetable
   group by PersonId
)
,totCounts as(  -- recursion  to count 5*3*2
   select PersonId,xCnt,grn, xCnt totCnt 
   from PersonCounts    where grn=1
   union all
   select t.PersonId,t.xCnt,t.grn,r.totCnt*t.xCnt 
   from totCounts r 
   inner join PersonCounts t on t.grn=(r.grn+1)
)
,rcTot as( -- take max value 5*3*2=30
  select *,max(totCnt)over()totOver from totCounts
)
,PrepRn as(  -- calculate indexes for every row fromN - toN
  select PersonId,TimetableID,rQty,rnx
    ,(rnx-1)*totOver/rqty+1 fromN
    ,rnx*totOver/rqty toN
    ,totOver
  from(
    select t.PersonID,TimetableID
      ,row_number()over(partition by t.PersonId order by t.TimetableID) rnx
      ,count(*)over(partition by t.PersonID ) rQty
      ,totOver
    from tblTimetable t
    inner join rcTot t2 on t2.PersonID=t.PersonID
  )a
)
select t.PersonId,TimetableID,fromN rnx,toN ,nn.value grn
    ,rQty,totOver
from PrepRn t
cross apply generate_series(fromN,toN)nn
order by grn,PersonId;

.....

violino

violino

Desde que você tenha acesso ao GENERATE_SERIES()SQL Server 2022 em diante, não precisará de recursão ou junções, apenas um pouco de matemática e duplicação de linhas com IDs apropriadas.

No seu caso, existem 30 combinações ( 5 from person A* 3 from person B* 2 from person c== 30)

Isto significa que:

• cada linha person Aprecisa ser duplicada 6 vezes

• cada linha person Bprecisa ser duplicada 10 vezes

• cada linha person Cprecisa ser duplicada 15 vezes

Cada duplicado precisa de um combo_id, por exemplo, para person A:

Isso pode ser obtido com GENERATE_SERIES(original_row_id, 30, 5)onde 30é o número total de combinações e 5é o número de linhas que a corrente personpossui.

Using CROSS APPLY means that we can generate a different series (generating different number of duplicates, with differing combination_d values) for each person and each of their rows.

CREATE TABLE timetable
(
  person_id      INT,
  timetable_id   INT
);


INSERT INTO
  timetable
VALUES
  (5215,   57), (5215,   67), (5215,   77), (5215,   87), (5215,   97),
  (18943, 221), (18943, 230), (18943, 238),
  (21488, 257), (21488, 270)
;

WITH
  summary (person_id, timetable_id, person_rows, person_row_ix)
AS
(
  SELECT
    person_id,
    timetable_id,
    COUNT(*) OVER (PARTITION BY person_id),
    CAST(ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY timetable_id) AS INT)
  FROM
    timetable
),
  mapping
AS
(
  SELECT
    *,
    -- PRODUCT AGGREGATE, in this case, 5*1*1*1*1 * 3*1*1 * 2*1 == 30
    CAST(
      ROUND(
        EXP(
          SUM(CASE WHEN person_row_ix = 1 THEN LOG(person_rows) ELSE 0 END)
          OVER ()
        ),
        0
      )
      AS INT
    )
      AS total_combinations
  FROM
    summary
)
SELECT
  value AS combination_id,
  mapping.*
FROM
  mapping
CROSS APPLY
  GENERATE_SERIES(
    person_row_ix,
    total_combinations,
    person_rows
  )
ORDER BY
  value,
  person_id

fiddle

Tente remover os hits para x da tabela original, como nesta consulta cte. Você pode preparar um procedimento armazenado com base nessa lógica ou adicionar recursividade para a consulta cte, se possível (desculpe, não sou bom nisso.)

with cte_a
as
(
    select
        x,
        min(y) as y1,
        count(*) as cnt
    from tblTmp
    group by x
),

cte_b
as
(
    select
        x,
        min(y) as y2,
        count(*) as cnt
    from tblTmp where y <> (select y1 from cte_a where cnt > 1)
    group by x
),

cte_c
as
(
    select
        x,
        min(y) as y,
        count(*) as cnt
    from tblTmp where y not in (select y1 from cte_a where cnt > 1 union select y2 from cte_b where cnt > 1)
    group by x
)



select * from cte_a union all select * from cte_b union all select * from cte_c

select *
from tblTimetable t1 cross join tblTimetable t2 cross join tblTimetable t3
where t1.PersonId < t2.PersonId and t2.PersonId < t3.PersonId;

Faça uma junção cruzada de 3 vias. Uma pessoa dada não será escolhida mais de uma vez por causa das desigualdades.