if object_id('[tb]') is not null drop table [tb] go create table [tb]([A] int,[B] int) insert [tb] select 1,32 union all select 1,64 union all select 2,12 union all select 2,15 union all select 2,15 union all select 2,70select id=identity(int),* into # from tb select px=(select count(1) from # where a=t.a and id<=t.id),* into ## from # tdeclare @s varchar(8000) set @s='select a ' select @s=@s+',max(case when px='+px+' then B else 0 end) ['+px+']' from (select distinct ltrim(px) px from ##) t select @s=@s+' from ## group by a' exec(@s) /* a 1 2 3 4 ----------- ----------- ----------- ----------- ----------- 1 32 64 0 0 2 12 15 15 70(6 row(s) affected) */drop table #,##
if object_id('[tb]') is not null drop table [tb] go create table [tb]([A] int,[B] int) insert [tb] select 1,32 union all select 1,64 union all select 2,12 union all select 2,15 union all select 2,15 union all select 2,70select id=identity(int),* into # from tb select px=(select count(1) from # where a=t.a and id<=t.id),* into ## from # tdeclare @s varchar(8000) set @s='select A ' select @s=@s+',max(case when px='+px+' then B else 0 end) [B'+px+']' from (select distinct ltrim(px) px from ##) t select @s=@s+' from ## group by a' exec(@s) /* A B1 B2 B3 B4 ----------- ----------- ----------- ----------- ----------- 1 32 64 0 0 2 12 15 15 70(6 row(s) affected) */drop table #,## SQL2005可以直接用 ROW_NUMBER() 函数
create table tb(A int,B int) insert into tb select 1,32 insert into tb select 1,64 insert into tb select 2,12 insert into tb select 2,15 insert into tb select 2,15 insert into tb select 2,70 go select id=identity(int,1,1),a,b into # from tb go select a, (select TOP 1 b from # t2 where a=t1.a and not exists(select 1 from # where a=t1.a and id>t2.id)) as B1, ISNULL((select TOP 1 b from # t2 where a=t1.a and not exists(select 1 from # where a=t1.a and id>t2.id+1)),0) as B2, ISNULL((select TOP 1 b from # t2 where a=t1.a and not exists(select 1 from # where a=t1.a and id>t2.id+2)),0) as B3, ISNULL((select TOP 1 b from # t2 where a=t1.a and not exists(select 1 from # where a=t1.a and id>t2.id+3)),0) as B4 from # t1 group by a go drop table tb,# /* a B1 B2 B3 B4 ----------- ----------- ----------- ----------- ----------- 1 64 32 32 32 2 70 15 15 12*/
go
create table [tb]([A] int,[B] int)
insert [tb] select 1,32
union all select 1,64
union all select 2,12
union all select 2,15
union all select 2,15
union all select 2,70select id=identity(int),* into # from tb
select px=(select count(1) from # where a=t.a and id<=t.id),* into ## from # tdeclare @s varchar(8000)
set @s='select a '
select @s=@s+',max(case when px='+px+' then B else 0 end) ['+px+']'
from (select distinct ltrim(px) px from ##) t
select @s=@s+' from ## group by a'
exec(@s)
/*
a 1 2 3 4
----------- ----------- ----------- ----------- -----------
1 32 64 0 0
2 12 15 15 70(6 row(s) affected)
*/drop table #,##
go
create table [tb]([A] int,[B] int)
insert [tb] select 1,32
union all select 1,64
union all select 2,12
union all select 2,15
union all select 2,15
union all select 2,70select id=identity(int),* into # from tb
select px=(select count(1) from # where a=t.a and id<=t.id),* into ## from # tdeclare @s varchar(8000)
set @s='select A '
select @s=@s+',max(case when px='+px+' then B else 0 end) [B'+px+']'
from (select distinct ltrim(px) px from ##) t
select @s=@s+' from ## group by a'
exec(@s)
/*
A B1 B2 B3 B4
----------- ----------- ----------- ----------- -----------
1 32 64 0 0
2 12 15 15 70(6 row(s) affected)
*/drop table #,##
SQL2005可以直接用 ROW_NUMBER() 函数
insert into tb select 1,32
insert into tb select 1,64
insert into tb select 2,12
insert into tb select 2,15
insert into tb select 2,15
insert into tb select 2,70
go
select id=identity(int,1,1),a,b into # from tb
go
select a,
(select TOP 1 b from # t2 where a=t1.a and not exists(select 1 from # where a=t1.a and id>t2.id)) as B1,
ISNULL((select TOP 1 b from # t2 where a=t1.a and not exists(select 1 from # where a=t1.a and id>t2.id+1)),0) as B2,
ISNULL((select TOP 1 b from # t2 where a=t1.a and not exists(select 1 from # where a=t1.a and id>t2.id+2)),0) as B3,
ISNULL((select TOP 1 b from # t2 where a=t1.a and not exists(select 1 from # where a=t1.a and id>t2.id+3)),0) as B4
from # t1
group by a
go
drop table tb,#
/*
a B1 B2 B3 B4
----------- ----------- ----------- ----------- -----------
1 64 32 32 32
2 70 15 15 12*/