SQLTeam.com | Weblogs | Forums

Showing results of one column into two columns

Greetings,

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.

Thank you very much in advance

do you need the result to be exactly as you have it or any combination will do?

The exact result is preferable :slight_smile:

Is there are any other columns that are associated with this list? What is the basis of their relationship?

hi

i tried to do this ... hope this helps :+1:

create table data ( name varchar(100) ) 
insert into data values
('John'),
('Nancy'),
('Jem'),
('Albert'),
('Smith'),
('Daniella')

; with cte as 
(
select ROW_NUMBER() over( order by (select null)) as rn  , * from data 
) 
, 
cte_odd as 
(
 select ROW_NUMBER() over( order by (select null)) as rn1,* from cte where Rn%2=1
 )
,
cte_even as 
(
 select ROW_NUMBER() over( order by (select null)) as rn1,* from cte where Rn%2=0
 )
 select 
        a.name
	  , b.name 
 from 
     cte_odd a  
	     join 
     cte_even b 
	     on a.rn1= b.rn1 

image

image

1 Like

Thank you very much harishgg1 .. perfect solution .. exactly as I wanted .. you are #1 :slight_smile:

It will eventually fail because there is absolutely nothing in the original list to preserve the order you want and you cannot rely on the "implicit" order in the table.