create table test2
(Roll_ID int
)

insert into test2 values(1)
insert into test2 values(2)
insert into test2 values(3)
insert into test2 values(4)
insert into test2 values(5)


i want output as

RollId

Roll_id(asc) Roll_id(desc)
1 5
2 4
3 3
4 2
5 1

as output i googled a lot but not getting

Let me know if this will do the trick for you.

select a.Roll_id, b.Roll_id from 
(select Roll_id, ROW_NUMBER() over (ORDER BY Roll_id) as 'RowNumber' 
from test2 ) a inner join 
(select Roll_id, ROW_NUMBER() over (ORDER BY Roll_id DESC) as 'RowNumber' 
from test2 ) b
on a.RowNumber = b.RowNumber
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.