Showing results of one column into two columns

Select Participants from table will result in

Participants
-------
John
Nancy
Jem
Albert
Smith
Daniella

I want a query where the results shows

Player   Opponent
------   --------
John      Nancy
Jem       Albert
Smith     Daniella

A good sample for this question is the query used in generating single-elimination tournament pairings.

Answer

You can use window functios.Just LEAD+ROW_NUMBER to get opponent and then get player with odd numbers

  SELECT player,Opponent FROM
 (
   SELECT player, Number, LEAD(player)OVER(ORDER BY Number)Opponent from
    (
       SELECT player, ROW_NUMBER()OVER (ORDER BY player)Number FROM Table
    )X
 )Y
   WHERE Number % 2 <> 0