How to swap two rows in sql server

madhanmohanchimmili 1 Reputation point

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

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,795 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 94,511 Reputation points

    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.

    2 people found this answer helpful.
    0 comments No comments

  2. EchoLiu-MSFT 14,516 Reputation points

    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')  
        ;WITH cte  
        as(SELECT *,CASE WHEN Id%2=0 THEN Id-1 ELSE Id+1 END nn   
        FROM #test)  
        SELECT,c.[name] FROM #test t  
        JOIN cte c ON  
        ORDER BY  


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


    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.

    1 person found this answer helpful.