id是连续的话 我自己也会写 tab1 a left join tab1 b on b.id=a.id+1 where a.id%2=1
既然会写 那就创造ID连续 思路都一样的 根据连续的ID判断 分两列
set nocount on declare @i int set @i=1 declare @t table(ID int,[name] varchar(10)) while @i<=100 begin insert @t select @i,char(64+@i)+'test' set @i=@i+1 end select max(case when (ID-1)%2=0 then ID else null end)[ID1], max(case when (ID-1)%2=0 then name else null end)[Name1], max(case when (ID-1)%2=1 then ID else null end)[ID2], max(case when (ID-1)%2=1 then name else null end)[Name2] from @t group by (ID-1)/2 /* ID1 Name1 ID2 Name2 ----------- ---------- ----------- ---------- 1 Atest 2 Btest 3 Ctest 4 Dtest 5 Etest 6 Ftest 7 Gtest 8 Htest 9 Itest 10 Jtest 11 Ktest 12 Ltest .................. */
tab1 a left join tab1 b on b.id=a.id+1 where a.id%2=1
那就创造ID连续
思路都一样的
根据连续的ID判断
分两列
declare @i int
set @i=1
declare @t table(ID int,[name] varchar(10))
while @i<=100
begin
insert @t select @i,char(64+@i)+'test'
set @i=@i+1
end
select
max(case when (ID-1)%2=0 then ID else null end)[ID1],
max(case when (ID-1)%2=0 then name else null end)[Name1],
max(case when (ID-1)%2=1 then ID else null end)[ID2],
max(case when (ID-1)%2=1 then name else null end)[Name2]
from @t
group by (ID-1)/2
/*
ID1 Name1 ID2 Name2
----------- ---------- ----------- ----------
1 Atest 2 Btest
3 Ctest 4 Dtest
5 Etest 6 Ftest
7 Gtest 8 Htest
9 Itest 10 Jtest
11 Ktest 12 Ltest
..................
*/
或者通过临时表
2005 可以通过row_number实现ID连续