user1934212 user1934212 - 26 days ago 7
SQL Question

Pair entry of every nth row with entry of every (n+1)th row

I have a result table

id | name | wins
----+-------------------
57 | Paul | 10
64 | Sven | 9
62 | Peter | 9
59 | Marina | 8
58 | Carlos | 4
60 | Pamela | 3
61 | Marcus | 2
63 | Hank | 1


Where I want to pair every nth entry with every (n+1)th entry, such that the resulting table looks like that:

id | name | id | name
----+-------------------
57 | Paul | 64 | Sven
62 | Peter | 59 | Marina
58 | Carlos | 60 | Pamela
61 | Marcus | 63 | Hank


Which SQL statement would achieve that?

Answer
;WITH cte AS (
    SELECT *,ROW_NUMBER() OVER (ORDER BY Wins DESC) as RowNum
    FROM
       @Table
)

SELECT *
FROM
    cte c1
    LEFT JOIN cte c2
    ON c1.RowNum + 1 = c2.RowNum
WHERE
    c1.RowNum % 2 <> 0

Generate a ROW_NUMBER to use, seeing you have a third Column replace (SELECT NULL) in the Order by statement with that third column.

Then select all rows that are Odd Row numbers (remainder of RowNum divided by 2 <> 0 ) and self join back to itself with RowNum + 1. If you have an odd number of Rows you might consider using LEFT JOIN so you don't drop off the 1 row that won't have a match.