demo, Sql Server - rextester

demo

Language: Layout:

DECLARE @Temp TABLE(
    TeamName VARCHAR(100),
    MatchWeek INT
)

INSERT INTO @Temp(TeamName) VALUES
('Team1'),
('Team2'),
('Team3'),
('Team4'),
('Team5'),
('Team6'),
('Team7'),
('Team8'),
('Team9'),
('Team10'),
('Team11'),
('Team12');

WITH Teams
     AS (SELECT TeamName,
                TeamNum = ROW_NUMBER() OVER (ORDER BY TeamName),
                TeamCount = COUNT(*) OVER()
         FROM   @Temp
		 /*Purpose of below is to add an extra dummy team if odd number 
		   of teams. This null team name will be matched up against competitors 
		   having no game that week */
         GROUP  BY TeamName WITH ROLLUP
         HAVING GROUPING(TeamName) = 0
                 OR COUNT(*) %2 = 1),
     Weeks
     AS ( /*We need numbers 1- 11 for a 12 team league etc. 
           Can use the row numbers calculated above for this*/
         SELECT TeamNum AS Week
         FROM   Teams
         WHERE  TeamNum < TeamCount),
     Positioned
     AS (SELECT TeamName,
                TeamNum,
                Week,
                position,
                TeamCount,
                /*Sum of opposing positions should add up to TeamCount + 1 so can calculate slot for grouping*/
                Slot = CASE WHEN position <= TeamCount / 2 THEN position ELSE TeamCount + 1 - position END
         FROM   Teams
                CROSS JOIN Weeks
                /*Uses scheduling algorithm from Wikipedia with the last team in fixed position
                  and all other teams rotating around (between positions 1 and 11 in 12 team example)*/
                CROSS APPLY (SELECT CASE
                                      WHEN TeamNum = TeamCount
                                        THEN TeamCount
                                      ELSE 1 + ( ( TeamNum + Week ) % ( TeamCount - 1 ) )
                                    END) CA(position)),
     Matches
     AS (SELECT Week,
                Slot,
                TeamCount,
                TopTeam = MAX(CASE WHEN position = slot THEN TeamName END),
                BottomTeam = MAX(CASE WHEN position <> slot THEN TeamName END)
         FROM   Positioned
         GROUP  BY Week,
                   Slot,
                   TeamCount)
SELECT CA.*
FROM   Matches
       CROSS APPLY (
                   /*Choose Home and Away from alternating Top and Bottom of pair to 
                     avoid long runs of either for a team*/
				   /*First two rows are for alternate weeks in the 1st half of the season */
                   SELECT TopTeam, BottomTeam, Week
                   WHERE  Week %2 = 0
                   UNION ALL
                   SELECT BottomTeam, TopTeam, Week
                   WHERE  Week %2 > 0
                   UNION ALL
                   /*For second half of the season just reversing the "Home" and "Away" teams */
                   SELECT BottomTeam, TopTeam, Week + TeamCount - 1
                   WHERE  Week %2 = 0
                   UNION ALL
                   SELECT TopTeam, BottomTeam, Week + TeamCount - 1
                   WHERE  Week %2 > 0) CA(HomeTeam, AwayTeam, Week)
/*Exclude any dummy matches if odd number of teams*/
WHERE  CA.AwayTeam IS NOT NULL
       AND CA.HomeTeam IS NOT NULL
ORDER  BY CA.Week;

View schema
Execution time: 0,03 sec, rows selected: 132, rows affected: 12, absolute service time: 0,22 sec

	HomeTeam	AwayTeam	Week
1	Team9	Team7	1
2	Team6	Team8	1
3	Team5	Team1	1
4	Team4	Team10	1
5	Team3	Team11	1
6	Team2	Team12	1
7	Team6	Team9	2
8	Team7	Team5	2
9	Team8	Team4	2
10	Team1	Team3	2
11	Team10	Team2	2
12	Team11	Team12	2
13	Team9	Team5	3
14	Team4	Team6	3
15	Team3	Team7	3
16	Team2	Team8	3
17	Team12	Team1	3
18	Team11	Team10	3
19	Team4	Team9	4
20	Team5	Team3	4
21	Team6	Team2	4
22	Team7	Team12	4
23	Team8	Team11	4
24	Team1	Team10	4
25	Team9	Team3	5
26	Team2	Team4	5
27	Team12	Team5	5
28	Team11	Team6	5
29	Team10	Team7	5
30	Team1	Team8	5
31	Team2	Team9	6
32	Team3	Team12	6
33	Team4	Team11	6
34	Team5	Team10	6
35	Team6	Team1	6
36	Team7	Team8	6
37	Team9	Team12	7
38	Team11	Team2	7
39	Team10	Team3	7
40	Team1	Team4	7
41	Team8	Team5	7
42	Team7	Team6	7
43	Team11	Team9	8
44	Team12	Team10	8
45	Team2	Team1	8
46	Team3	Team8	8
47	Team4	Team7	8
48	Team5	Team6	8
49	Team9	Team10	9
50	Team1	Team11	9
51	Team8	Team12	9
52	Team7	Team2	9
53	Team6	Team3	9
54	Team5	Team4	9
55	Team1	Team9	10
56	Team10	Team8	10
57	Team11	Team7	10
58	Team12	Team6	10
59	Team2	Team5	10
60	Team3	Team4	10
61	Team9	Team8	11
62	Team7	Team1	11
63	Team6	Team10	11
64	Team5	Team11	11
65	Team4	Team12	11
66	Team3	Team2	11
67	Team12	Team2	12
68	Team11	Team3	12
69	Team10	Team4	12
70	Team1	Team5	12
71	Team8	Team6	12
72	Team7	Team9	12
73	Team5	Team7	13
74	Team9	Team6	13
75	Team12	Team11	13
76	Team2	Team10	13
77	Team3	Team1	13
78	Team4	Team8	13
79	Team8	Team2	14
80	Team7	Team3	14
81	Team6	Team4	14
82	Team5	Team9	14
83	Team10	Team11	14
84	Team1	Team12	14
85	Team3	Team5	15
86	Team9	Team4	15
87	Team10	Team1	15
88	Team11	Team8	15
89	Team12	Team7	15
90	Team2	Team6	15
91	Team8	Team1	16
92	Team7	Team10	16
93	Team6	Team11	16
94	Team5	Team12	16
95	Team4	Team2	16
96	Team3	Team9	16
97	Team12	Team3	17
98	Team9	Team2	17
99	Team8	Team7	17
100	Team1	Team6	17
101	Team10	Team5	17
102	Team11	Team4	17
103	Team4	Team1	18
104	Team3	Team10	18
105	Team2	Team11	18
106	Team12	Team9	18
107	Team6	Team7	18
108	Team5	Team8	18
109	Team10	Team12	19
110	Team9	Team11	19
111	Team6	Team5	19
112	Team7	Team4	19
113	Team8	Team3	19
114	Team1	Team2	19
115	Team4	Team5	20
116	Team3	Team6	20
117	Team2	Team7	20
118	Team12	Team8	20
119	Team11	Team1	20
120	Team10	Team9	20
121	Team8	Team10	21
122	Team9	Team1	21
123	Team4	Team3	21
124	Team5	Team2	21
125	Team6	Team12	21
126	Team7	Team11	21
127	Team11	Team5	22
128	Team10	Team6	22
129	Team1	Team7	22
130	Team8	Team9	22
131	Team2	Team3	22
132	Team12	Team4	22