create table T(A varchar(20),B int)
insert into T select '张三',1
insert into T select '李四',1
insert into T select 'Tom' ,2
insert into T select 'Jane',2select identity(int,1,1) as nid,0 as nid1,* into #T from Tupdate a
set
nid1=(select count(*) from #T where B=a.B and nid<=a.nid)
from #T aselect
max(case B when 1 then A end),
max(case B when 2 then A end)
from
#T
group by
nid1drop table T,#T
insert into T select '张三',1
insert into T select '李四',1
insert into T select 'Tom' ,2
insert into T select 'Jane',2select identity(int,1,1) as nid,0 as nid1,* into #T from Tupdate a
set
nid1=(select count(*) from #T where B=a.B and nid<=a.nid)
from #T aselect
max(case B when 1 then A end),
max(case B when 2 then A end)
from
#T
group by
nid1drop table T,#T
max(case B when 1 then A end)as '1'
,max(case B when 2 then A end)as '2'
from @T
union
select
min(case B when 1 then A end)as '1'
,min(case B when 2 then A end)as '2'
from @T