MSSQL - Tutorial 103 - Question

CREATE TABLE Teams
(
  Id INT,
  TeamName NVARCHAR(100)
)
GO

INSERT INTO Teams VALUES (1, 'INDIA')
INSERT INTO Teams VALUES (2, 'PAKISTAN')
INSERT INTO Teams VALUES (3, 'AUSTRALIA')
INSERT INTO Teams VALUES (4, 'SOUTH AFRICA')
INSERT INTO Teams VALUES (5, 'NEW ZEALAND')
GO


SELECT
  ROW_NUMBER() OVER (ORDER BY TeamA) AS MatchNo,
  TeamA,
  TeamB
  FROM (
    SELECT DISTINCT
      Id,
      FIRST_VALUE(TeamA) OVER (PARTITION BY Id ORDER BY Id) AS TeamA,
      FIRST_VALUE(TeamB) OVER (PARTITION BY Id ORDER BY Id) AS TeamB
      FROM (
        SELECT
          CONVERT(INT, (CONVERT(NVARCHAR(5), CASE WHEN tc.TAId < tc.TBId THEN tc.TAId ELSE tc.TBId END)
            + CONVERT(NVARCHAR(5), CASE WHEN tc.TAId < tc.TBId THEN tc.TBId ELSE tc.TAId END))) AS Id,
          tc.TeamA,
          tc.TeamB
          FROM (
            SELECT t1.Id AS TAId, t2.Id AS TBId, dbo.fn_TeamCode(t1.TeamName) AS TeamA, dbo.fn_TeamCode(t2.TeamName) AS TeamB
              FROM Teams t1
              CROSS JOIN Teams t2
              WHERE t1.TeamName != t2.TeamName) AS tc
      ) AS tc GROUP BY Id, TeamA, TeamB
  ) AS tc


CREATE FUNCTION fn_TeamCode (@str NVARCHAR(MAX))
  RETURNS NVARCHAR(MAX) AS
BEGIN
  DECLARE @firstLetters NVARCHAR(MAX)
  SET @str = RTRIM(LTRIM(@str));

  IF (CHARINDEX(' ', @str, 1) = 0)
  BEGIN
    SET @firstLetters = LEFT(@str, 3)
    RETURN @firstLetters
  END
  ELSE
    SET @firstLetters = LEFT(@str, 1)

  WHILE CHARINDEX(' ', @str, 1) > 0
  BEGIN
    SET @str = LTRIM(RIGHT(@str, LEN(@str) - CHARINDEX(' ', @str, 1)))
    SET @firstLetters += LEFT(@str, 1)
  END

  RETURN @firstLetters
END
GO