Creating random pairs from one column

Question

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.

Answer 1

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

Answer 2

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.