How to swap two rows in sql server

Madhan Chimmili 7 Reputation points
2021-09-28T13:27:36.797+00:00

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.
13,784 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,637 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Viorel 117.2K Reputation points
    2021-09-28T13:32:27.223+00:00

    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.

    3 people found this answer helpful.
    0 comments No comments

  2. EchoLiu-MSFT 14,581 Reputation points
    2021-09-29T01:25:25.367+00:00

    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:
    135990-image.png

    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.

    1 person found this answer helpful.

  3. Raja ❤️ 0 Reputation points
    2024-09-29T04:36:33.8466667+00:00

    The code is not running

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.