SHARE
TWEET

MSSQL - Tutorial 103 - Question

rvnlord Feb 3rd, 2020 280 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE Teams
  2. (
  3.   Id INT,
  4.   TeamName NVARCHAR(100)
  5. )
  6. GO
  7.  
  8. INSERT INTO Teams VALUES (1, 'INDIA')
  9. INSERT INTO Teams VALUES (2, 'PAKISTAN')
  10. INSERT INTO Teams VALUES (3, 'AUSTRALIA')
  11. INSERT INTO Teams VALUES (4, 'SOUTH AFRICA')
  12. INSERT INTO Teams VALUES (5, 'NEW ZEALAND')
  13. GO
  14.  
  15.  
  16. SELECT
  17.   ROW_NUMBER() OVER (ORDER BY TeamA) AS MatchNo,
  18.   TeamA,
  19.   TeamB
  20.   FROM (
  21.     SELECT DISTINCT
  22.       Id,
  23.       FIRST_VALUE(TeamA) OVER (PARTITION BY Id ORDER BY Id) AS TeamA,
  24.       FIRST_VALUE(TeamB) OVER (PARTITION BY Id ORDER BY Id) AS TeamB
  25.       FROM (
  26.         SELECT
  27.           CONVERT(INT, (CONVERT(NVARCHAR(5), CASE WHEN tc.TAId < tc.TBId THEN tc.TAId ELSE tc.TBId END)
  28.             + CONVERT(NVARCHAR(5), CASE WHEN tc.TAId < tc.TBId THEN tc.TBId ELSE tc.TAId END))) AS Id,
  29.           tc.TeamA,
  30.           tc.TeamB
  31.           FROM (
  32.             SELECT t1.Id AS TAId, t2.Id AS TBId, dbo.fn_TeamCode(t1.TeamName) AS TeamA, dbo.fn_TeamCode(t2.TeamName) AS TeamB
  33.               FROM Teams t1
  34.               CROSS JOIN Teams t2
  35.               WHERE t1.TeamName != t2.TeamName) AS tc
  36.       ) AS tc GROUP BY Id, TeamA, TeamB
  37.   ) AS tc
  38.  
  39.  
  40. CREATE FUNCTION fn_TeamCode (@str NVARCHAR(MAX))
  41.   RETURNS NVARCHAR(MAX) AS
  42. BEGIN
  43.   DECLARE @firstLetters NVARCHAR(MAX)
  44.   SET @str = RTRIM(LTRIM(@str));
  45.  
  46.   IF (CHARINDEX(' ', @str, 1) = 0)
  47.   BEGIN
  48.     SET @firstLetters = LEFT(@str, 3)
  49.     RETURN @firstLetters
  50.   END
  51.   ELSE
  52.     SET @firstLetters = LEFT(@str, 1)
  53.  
  54.   WHILE CHARINDEX(' ', @str, 1) > 0
  55.   BEGIN
  56.     SET @str = LTRIM(RIGHT(@str, LEN(@str) - CHARINDEX(' ', @str, 1)))
  57.     SET @firstLetters += LEFT(@str, 1)
  58.   END
  59.  
  60.   RETURN @firstLetters
  61. END
  62. GO
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top