I want to create random pairs from single column values. For example I have 6 rows in one table:
[First name] JHON LEE SAM HARRY JIM KRUK I want result like
[First name1] [First name2] Jhon Harry LEE KRUK HARRY SAM All first names should join randomly with each other resulting in pairs of two. The results should vary between executions.
To pair people up randomly you can use
With RandonlyNumbered AS ( SELECT FirstName, RN = ROW_NUMBER() OVER (ORDER BY CRYPT_GEN_RANDOM(4))-1 FROM dbo.Names ) SELECT Name1 = MAX(CASE WHEN RN%2 = 0 THEN FirstName END), Name2 = MAX(CASE WHEN RN%2 = 1 THEN FirstName END) FROM RandonlyNumbered GROUP BY RN/2 It first assigns a sequential zero based numbering to each name in random order and then assigns people in pairings according to that ordering.
If you have an odd number of names then of course one of the pairings will have a null Name2 as this does not divide evenly.
One issue with the above is that even though an ordering by RN will also supply rows ordered by RN/2 SQL Server does not take advantage of this in the GROUP BY RN/2 - so below is a version which does leverage this better and only contains one sort operation in the execution plan...
With T1 AS ( SELECT FirstName, RN = ROW_NUMBER() OVER (ORDER BY CRYPT_GEN_RANDOM(4))-1 FROM dbo.Names ), T2 AS ( SELECT Name1 = FirstName, Name2 = LEAD(FirstName) OVER (ORDER BY RN), RN FROM T1 ) SELECT Name1, Name2 FROM T2 WHERE RN % 2 = 0 One semi-random and fairly easy solution is you can use a window function like ROW_NUMBER() ordered over a previously generated unique set of GUIDs like so:
DROP TABLE IF EXISTS dbo.Names; CREATE TABLE dbo.Names (FirstName VARCHAR(100)); INSERT INTO dbo.Names VALUES ('JHON'), ('LEE'), ('SAM'), ('HARRY'), ('JIM'), ('KRUK'); WITH _NamesWithRandomRowIds AS ( SELECT FirstName, ROW_NUMBER() OVER (ORDER BY RandomGUID) AS RandomRowId FROM ( SELECT FirstName, NEWID() AS RandomGUID FROM dbo.Names ) AS NamesWithGUIDs ) SELECT N1.FirstName AS FirstName1, N2.FirstName AS FirstName2 FROM _NamesWithRandomRowIds AS N1 INNER JOIN _NamesWithRandomRowIds AS N2 ON N1.RandomRowId = N2.RandomRowId; Here's the dbfiddle demonstrating it in action.
Note in your example results, you only showed 3 rows. Note sure if that's intentional or you were just showing a subset of what the results should look like. If the former, you essentially can just add a TOP 3 after the final SELECT statement above to emulate the same thing.
