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
         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 - 1 ) % ( TeamCount - 1 ) )
                                    END) CA(position))
SELECT V.*
FROM   Positioned P1
       JOIN Positioned P2
         ON P1.Week = P2.Week
            /*Sum of positions should add up to TeamCount + 1*/
            AND P1.position = 1 + P2.TeamCount - P2.position
            /*Choose Home and Away from alternating Top and Bottom of pair to 
              avoid long runs of either for a team*/
            AND (P2.Week %2 = 0 AND P1.position < P2.position
                 OR P2.Week %2 = 1 AND P1.position > P2.position)
            /*For second half of the season just reversing the "Home" and "Away" teams */
       CROSS APPLY ( VALUES(P1.TeamName, P2.TeamName, P1.Week),
                           (P2.TeamName, P1.TeamName, P1.Week + P1.TeamCount - 1) ) V(HomeTeam, AwayTeam, Week)
/*Exclude any dummy matches if odd number of teams*/
WHERE  V.AwayTeam IS NOT NULL
       AND V.HomeTeam IS NOT NULL
ORDER  BY V.Week

View schema
Execution time: 0,06 sec, rows selected: 132, rows affected: 12, absolute service time: 0,25 sec

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