--===== Declare and set the number of players present.
     -- This could be a parameter in a stored procedure or function.
DECLARE @NumberOfPlayers INT;
 SELECT @NumberOfPlayers = 7; --Obviously, there will be a "BYE" for the 8th position.

--===== If the number of players is an odd number, add 1 to the number of players
 SELECT @NumberOfPlayers = @NumberOfPlayers + (@NumberOfPlayers %2);

--===== Create the "Round Robin" schedule using a Triangular Join (a limited form of CROSS JOIN).
     -- A better way to do this would be to use a Tally Table instead of master.dbo.spt_values.
     -- Please see the following article for that.
     -- http://www.sqlservercentral.com/articles/T-SQL/62867/
WITH
cteTally AS
(
 SELECT PlayerNumber = Number
   FROM master.dbo.spt_values
  WHERE Type = 'P'
    AND Number BETWEEN 1 AND @NumberOfPlayers
)
 SELECT t1.PlayerNumber, t2.PlayerNumber
   FROM cteTally t1
  INNER JOIN cteTally t2
     ON t1.PlayerNumber < t2.PlayerNumber
;

--===== Do this in a nice safe place that everyone has
    USE TempDB;
     IF OBJECT_ID('TempDB..Tally','U') IS NOT NULL
        DROP TABLE Tally;
GO

--===================================================================
--      Create a Tally table from 0 to 11000
--===================================================================
--===== Create and populate the Tally table on the fly.
 SELECT TOP 11001
        IDENTITY(INT,0,1) AS N
   INTO dbo.Tally
   FROM Master.sys.ALL_Columns ac1
  CROSS JOIN Master.sys.ALL_Columns ac2
;
--===== Add a CLUSTERED Primary Key to maximize performance
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;
--===== Allow the general public to use it
  GRANT SELECT ON dbo.Tally TO PUBLIC
;
GO

/**********************************************************************************************************************
 Purpose:
 Given virtually any number of teams > 0, create a "Round Robin" schedule.

 Notes:
 1. This code does NOT cycle the last team through "Match" positions (which could be lane pairs for a bowling league).
    Separate code would be need for that.
 2. This code could be converted to an function or a stored procedure.

 Revision History:
 Rev 00 - 17 Jan 2012 - Jeff Moden
**********************************************************************************************************************/
--===== Do this in a nice safe place that everyone has
    USE TempDB;

--===== Declare and set the number of players present.
     -- This could be a parameter in a stored procedure or function.
DECLARE @Teams INT;
 SELECT @Teams = 6; --<<< This could be a parameter in a function or a proc

--===== Conditionally drop the working table 
     IF OBJECT_ID('tempdb..#Team','U') IS NOT NULL
        DROP TABLE #Team
;
--===== If the number of players is an odd number, add 1 to the number of players
 SELECT @Teams = @Teams + (@Teams % 2)
;
--===== Create the team/reference table table with a preset "Reference" to act as a "ring counter"
 SELECT Team      = t.N,
        Reference = CASE --This is a zero based number
                    WHEN t.N = @Teams   THEN @Teams - 1                     --Last position is always last count
                    WHEN t.N % 2 = 1    THEN t.N/2                          --Count odd forwards
                    WHEN t.N % 2 = 0    THEN (@Teams-t.N)/2+(@Teams/2)-1    --Count all but last even backwards
                    END,
        Match     = (t.N - 1) / 2 + 1
   INTO #Team
   FROM dbo.Tally t
  WHERE t.N >= 1 AND t.N <= @Teams
;
--===== Produce the "Round Robin" schedule
WITH
cteBuildRounds AS
( --=== This builds the round information
 SELECT [Round]   = t.N + 1,
        Reference = CASE
                    WHEN robin.Reference < (@Teams - 1) THEN (robin.Reference + t.N) % (@Teams - 1)
                    ELSE robin.Reference
                    END,
        Match     = robin.Match,
        Side      = (robin.Team - 1) % 2
   FROM #Team robin
  CROSS JOIN dbo.Tally t
  WHERE t.N BETWEEN 0 AND (@Teams - 2)
) --=== This simply summarizes by match
 SELECT br.[Round],
        br.Match,
        Teams = MAX(CASE WHEN br.Side = 0 THEN CAST(team.Team AS VARCHAR(10)) ELSE '' END) + ' - '
              + MAX(CASE WHEN br.Side = 1 THEN CAST(team.Team AS VARCHAR(10)) ELSE '' END)
   FROM cteBuildRounds br
  INNER JOIN #Team team
     ON team.Reference = br.Reference
  GROUP BY br.[Round], br.Match
  ORDER BY br.[Round], br.Match
;