SQL Server 2016: Query to create multiple unique pairs of IDs from the same table?

144 Views Asked by At

I'm working on building a query to handle random pairings of people so that each one can assess many others. I'm looking for a way to handle this in bulk - cross join perhaps? -rather than using a cursor to loop through people one at a time, which when tested was pretty slow as there will likely be hundreds of pairings at a time.

There are a few main parameters:

  1. Each pair must be unique - two IDs can only be paired once.
  2. There will be a specific number of pairs per ID - both the person being assessed and the person doing the assessing can have no more or less than the specific number of pairs.
  3. All IDs are in this one table.
  4. Must be able to create the pairs in random order rather.
  5. No ID can be paired with itself.

Any ideas for how I could approach this?

Here's the query I've been working on

DECLARE @assessmentID INT=[N];
DECLARE @assessmentPairs TABLE( 
    assessorID INT,
    authorID INT,
    assessorCounter INT,
    authorCounter INT
    UNIQUE NONCLUSTERED ([assessorID], [authorID])
);

INSERT INTO @assessmentPairs
SELECT assessorID,authorID,assessorCounter,authorCounter
FROM (
    SELECT 
        e1.personID AS assessorID,
        e2.personID AS authorID,
        assessorCounter=ROW_NUMBER() OVER(PARTITION BY e1.personID ORDER BY e1.personID),
        authorCounter=ROW_NUMBER() OVER(PARTITION BY e2.personID ORDER BY NEWID())
    FROM People e1
    JOIN Assessments a ON a.courseOfferingID=e1.courseOfferingID
    CROSS JOIN People e2
    WHERE e2.personID<>e1.personID 
    AND a.assessmentID=@assessmentID
    GROUP BY e1.personID,e2.personID
) AS x
WHERE authorCounter<=10
ORDER BY assessorID,authorCounter,authorID,assessorCounter

SELECT *
FROM @assessmentPairs
ORDER BY authorID,assessorID
0

There are 0 best solutions below