关于行值交换的任务 [英] questing regarding the row value interchange

问题描述

如何在sql中交换表的列的备用行的值？ （即row1value变为row2value而row2value变为row1value，类似row3value变为row4value而row4value变为row3value）

how can i interchange the value of alternate rows of a column of a table in sql? (i.e row1value becomes row2value and row2value becomes row1value similarly row3value becomes row4value and row4value becomes row3value)

推荐答案

i已经进行了示例查询为你检查一下，希望这会对你有所帮助。

Hi,
i have made a sample query for you check this,Hope this will help you.

-- Create sample table.

create table Interchanges
(rowNum  int,
 rowvalue  varchar(40))

-- insert sample data
 insert into Interchanges(rownum,rowvalue) values (1,'row1')
 insert into Interchanges(rownum,rowvalue) values (2,'row2')
 insert into Interchanges(rownum,rowvalue) values (3,'row3')
 insert into Interchanges(rownum,rowvalue) values (4,'row4')
 insert into Interchanges(rownum,rowvalue) values (5,'row5')
 insert into Interchanges(rownum,rowvalue) values (6,'row6')

-- Select query 
SELECT  * FROM Interchanges 

-- The out but will be like this : 
--rowNum         rowvalue
--1		row2
--2		row1
--3		row4
--4		row3
--5		row6
--6		row5

-- Used CTE to update swap row data
-- run both cte and update query at same time

;with cte as
   (
     SELECT distinct rownum ,case when rownum+1=2 then 2 else rownum+1 end as rownewval, rowvalue FROM Interchanges  WHERE rownum % 2 = 1  
   UNION  ALL
   SELECT distinct rownum ,case when rownum-1=0 then 1 else rownum-1 end as rownewval, rowvalue FROM Interchanges  WHERE rownum % 2 = 0 
    )
update Interchanges set
Interchanges.rowvalue= upval.rowvalue
from Interchanges  ,cte upval
where Interchanges.rownum=upval.rownewval

-- Select query 
SELECT  * FROM Interchanges 

-- The Final result as you expected

----rowNum             rowvalue
--1			row2
--2			row1
--3			row4
--4			row3
--5			row6
--6			row5

这篇关于关于行值交换的任务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！