How to swap two rows in sql server

Question

say ex:
Id name
1 a
2 b
3 c
4 d
5 e
6 f

i want output like:

Id name
1 b
2 a
3 d
4 c
5 f
6 e

Please help me how to write a SQL query

Answer 1

One of solutions:

select Id,
    case when row_number() over (order by Id) % 2 = 1
    then lead(name) over (order by Id)
    else lag(name) over (order by Id) end as name
from MyTable

If there are no gaps, you can use Id instead of row_number.

Answer 2

Hi @madhanmohanchimmili ,

Welcome to the microsoft TSQL Q&A forum!

Please also check:

    CREATE TABLE #test(Id int,[name] varchar(5))  
    INSERT INTO #test VALUES(1,'a'),(2,'b'),(3,'c')  
    ,(4,'d'),(5,'e'),(6,'f')  
      
      
    ;WITH cte  
    as(SELECT *,CASE WHEN Id%2=0 THEN Id-1 ELSE Id+1 END nn   
    FROM #test)  
      
    SELECT t.id,c.[name] FROM #test t  
    JOIN cte c ON t.id=c.nn  
    ORDER BY t.id  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

The code is not running