How to select all combinaisons of PersonID, TimetableID pairs from a time table

In a workshop 3 or more persons work together with different timetables. For example, 3 PersonID's are 5215, 18943 and 21488.

I have a table tblTimetable like this:

CREATE TABLE tblTimetable
(
    PersonID INT,
    TimetableID INT
);


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

With this select:

SELECT PersonID, TimetableID
FROM tblTimetable

I get this set of source rows:

PersonID    TimetableID
5215        57
18943       221
18943       230
18943       238
21488       257
21488       270
5215        67
5215        77
5215        87
5215        97

Each pair (PersonID, TimetableID) is an unchangeable solid block. I would like to select all combinations of pairs (PersonID, TimetableID).

Each TimetableID is defined in a separated table tblTimetableDetail, here it is not handled, but it will be queried later for plotting on a Microsoft Access report.

CREATE TABLE tblTimetableDetail
(
    TimetableID INT,
    WeekdayID INT,
    BeginTime DATETIME,
    EndTime DATETIME
);

So far I tried this:

SELECT PersonID, MIN(TimetableID) 
FROM tblTimetable
GROUP BY PersonID

UNION ALL

SELECT PersonID, MAX(TimetableID) 
FROM tblTimetable
GROUP BY PersonID

I got two combinations of PersonID, TimetableID pairs:

PersonID    TimetableID
5215    57
18943   221
21488   257

5215    97
18943   238
21488   270

I would like to get the exhaustive list of combinations of pairs PersonID - TimetableID, all PersonID's must be present in any combination. But the order does not matter:

CombiNo PersonID TimetableID
1       5215       57
1       18943      221
1       21488      257

2       5215       57
2       18943      221
2       21488      270

3       5215       57
3       18943      230
3       21488      257

4       5215       57
4       18943      230
4       21488      270

5       5215       57
5       18943      238
5       21488      257

6       5215       57
6       18943      238
6       21488      270



7       5215       67
7       18943      221
7       21488      257

8       5215       67
8       18943      221
8       21488      270

9       5215       67
9       18943      230
9       21488      257

10      5215       67
10      18943      230
10      21488      270

11      5215       67
11      18943      238
11      21488      257

12      5215       67
12      18943      238
12      21488      270


13       5215      77
13       18943     221
13       21488     257

14       5215      77
14       18943     221
14       21488     270

15       5215       77
15       18943    230
15       21488    257

16      5215       77
16      18943      230
16      21488      270

17      5215       77
17      18943      238
17      21488      257

18      5215       77
18      18943      238
18      21488      270


19       5215      87
19       18943     221
19       21488     257

20       5215      87
20       18943     221
20       21488     270

21       5215       87
21       18943    230
21       21488    257

22      5215       87
22      18943      230
22      21488      270

23      5215       87
23      18943      238
23      21488      257

24      5215       87
24      18943      238
24      21488      270


25       5215      97
25       18943     221
25       21488     257

26       5215      97
26       18943     221
26       21488     270

27       5215       97
27       18943    230
27       21488    257

28      5215       97
28      18943      230
28      21488      270

29      5215       97
29      18943      238
29      21488      257

30      5215       97
30      18943      238
30      21488      270

How to achieve this with T-SQL?

With a good SQL string, I can make a PassThrough query as .RecordSource for a report, drawing 30 pages of timetable, each combination per page.

Solution

fiddle

Provided that you have access to GENERATE_SERIES() from SQL Server 2022 onwards, you don't need recursion or joins, just a little bit of maths and duplicating rows with appropriate IDs.

In your case, there are 30 combinations (5 from person A * 3 from person B * 2 from person c == 30)

This means that:

each row from person A needs duplicating 6 times
each row from person B needs duplicating 10 times
each row from person C needs duplicating 15 times

Each duplicate needs a different combo_id, for example, for person A:

Original Row#	1st combo#	2nd combo#	3rd combo#	4th combo#	5th combo#	6th combo#
1	1	6	11	16	21	26
2	2	7	12	17	22	27
3	3	8	13	18	23	28
4	4	9	14	19	24	29
5	5	10	15	20	25	30

This can be achieved with GENERATE_SERIES(original_row_id, 30, 5) where 30 is the total number of combinations, and 5 is the number of rows the current person has.

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

combination_id	person_id	timetable_id	person_rows	person_row_ix	total_combinations
1	5215	57	5	1	30
1	18943	221	3	1	30
1	21488	257	2	1	30
2	5215	67	5	2	30
2	18943	230	3	2	30
2	21488	270	2	2	30
3	5215	77	5	3	30
3	18943	238	3	3	30
3	21488	257	2	1	30
4	5215	87	5	4	30
4	18943	221	3	1	30
4	21488	270	2	2	30
5	5215	97	5	5	30
5	18943	230	3	2	30
5	21488	257	2	1	30
6	5215	57	5	1	30
6	18943	238	3	3	30
6	21488	270	2	2	30
7	5215	67	5	2	30
7	18943	221	3	1	30
7	21488	257	2	1	30
8	5215	77	5	3	30
8	18943	230	3	2	30
8	21488	270	2	2	30
9	5215	87	5	4	30
9	18943	238	3	3	30
9	21488	257	2	1	30
10	5215	97	5	5	30
10	18943	221	3	1	30
10	21488	270	2	2	30
11	5215	57	5	1	30
11	18943	230	3	2	30
11	21488	257	2	1	30
12	5215	67	5	2	30
12	18943	238	3	3	30
12	21488	270	2	2	30
13	5215	77	5	3	30
13	18943	221	3	1	30
13	21488	257	2	1	30
14	5215	87	5	4	30
14	18943	230	3	2	30
14	21488	270	2	2	30
15	5215	97	5	5	30
15	18943	238	3	3	30
15	21488	257	2	1	30
16	5215	57	5	1	30
16	18943	221	3	1	30
16	21488	270	2	2	30
17	5215	67	5	2	30
17	18943	230	3	2	30
17	21488	257	2	1	30
18	5215	77	5	3	30
18	18943	238	3	3	30
18	21488	270	2	2	30
19	5215	87	5	4	30
19	18943	221	3	1	30
19	21488	257	2	1	30
20	5215	97	5	5	30
20	18943	230	3	2	30
20	21488	270	2	2	30
21	5215	57	5	1	30
21	18943	238	3	3	30
21	21488	257	2	1	30
22	5215	67	5	2	30
22	18943	221	3	1	30
22	21488	270	2	2	30
23	5215	77	5	3	30
23	18943	230	3	2	30
23	21488	257	2	1	30
24	5215	87	5	4	30
24	18943	238	3	3	30
24	21488	270	2	2	30
25	5215	97	5	5	30
25	18943	221	3	1	30
25	21488	257	2	1	30
26	5215	57	5	1	30
26	18943	230	3	2	30
26	21488	270	2	2	30
27	5215	67	5	2	30
27	18943	238	3	3	30
27	21488	257	2	1	30
28	5215	77	5	3	30
28	18943	221	3	1	30
28	21488	270	2	2	30
29	5215	87	5	4	30
29	18943	230	3	2	30
29	21488	257	2	1	30
30	5215	97	5	5	30
30	18943	238	3	3	30
30	21488	270	2	2	30

fiddle