有一个标志列,与a列对应比如: a id 1 0 2 1 3 0 4 1同一个表中将id为1的变为b列,将id为0的变为c列如果两列行数不一样,少的那列用null补足
select a.a as b,b.a as c from (select rn=row_number() over(order by getdate()),a from tb where id=0) a full join (select rn=row_number() over(order by getdate()),a from tb where id=1) b on a.rn=b.rn
select max(case id when 1 then a else '' end ) as b, max(case id when 0 then a else '' end ) as c from tb
create table tb(a int,id int) insert into tb select 1,0 union all select 2,1 union all select 3,0 union all select 4,1 union all select 5,0 goselect a.a,b.a from (select a,id as b from tb where id = 0)a full join (select a,id as c from tb where id = 1)b on a.a + 1 = b.adrop table tb/* a a ----------- ----------- 1 2 3 4 5 NULL(3 行受影响)
有一个标志列,与a列对应比如:
a id
1 0
2 1
3 0
4 1同一个表中将id为1的变为b列,将id为0的变为c列如果两列行数不一样,少的那列用null补足
from
(select rn=row_number() over(order by getdate()),a from tb where id=0) a
full join
(select rn=row_number() over(order by getdate()),a from tb where id=1) b
on a.rn=b.rn
max(case id when 1 then a else '' end ) as b,
max(case id when 0 then a else '' end ) as c
from
tb
create table tb(a int,id int)
insert into tb
select 1,0 union all
select 2,1 union all
select 3,0 union all
select 4,1 union all
select 5,0
goselect a.a,b.a
from (select a,id as b from tb where id = 0)a
full join
(select a,id as c from tb where id = 1)b
on a.a + 1 = b.adrop table tb/*
a a
----------- -----------
1 2
3 4
5 NULL(3 行受影响)