declare @t table(cuno char(2),[percent] numeric(10,4),empl_name int,empl_dept char(3))
insert @t
select 'a1',0.2,1,'001' union all
select 'a1',0.8,5,'002' union all
select 'a2',0.3,2,'005' union all
select 'a2',0.2,54,'007' union all
select 'a2',0.5,32,'004'select cuno,
max(case rid when 0 then [percent] end) as percent1,max(case rid when 0 then [empl_name] end) as empl_name1,max(case rid when 0 then [empl_dept] end) as empl_dept1,
max(case rid when 1 then [percent] end) as percent2,max(case rid when 1 then [empl_name] end) as empl_name2,max(case rid when 1 then [empl_dept] end) as empl_dept2,
max(case rid when 2 then [percent] end) as percent3,max(case rid when 2 then [empl_name] end) as empl_name3,max(case rid when 2 then [empl_dept] end) as empl_dept3
from (
select *,
(select count(*) from @t where cuno = a.cuno and empl_dept < a.empl_dept) as rid
from @t a) aa
group by cuno/*cuno percent1 empl_name1 empl_dept1 percent2 empl_name2 empl_dept2 percent3 empl_name3 empl_dept3
---- ------------ ----------- ---------- ------------ ----------- ---------- ------------ ----------- ----------
a1 .2000 1 001 .8000 5 002 NULL NULL NULL
a2 .5000 32 004 .3000 2 005 .2000 54 007*/
insert @t
select 'a1',0.2,1,'001' union all
select 'a1',0.8,5,'002' union all
select 'a2',0.3,2,'005' union all
select 'a2',0.2,54,'007' union all
select 'a2',0.5,32,'004'select cuno,
max(case rid when 0 then [percent] end) as percent1,max(case rid when 0 then [empl_name] end) as empl_name1,max(case rid when 0 then [empl_dept] end) as empl_dept1,
max(case rid when 1 then [percent] end) as percent2,max(case rid when 1 then [empl_name] end) as empl_name2,max(case rid when 1 then [empl_dept] end) as empl_dept2,
max(case rid when 2 then [percent] end) as percent3,max(case rid when 2 then [empl_name] end) as empl_name3,max(case rid when 2 then [empl_dept] end) as empl_dept3
from (
select *,
(select count(*) from @t where cuno = a.cuno and empl_dept < a.empl_dept) as rid
from @t a) aa
group by cuno/*cuno percent1 empl_name1 empl_dept1 percent2 empl_name2 empl_dept2 percent3 empl_name3 empl_dept3
---- ------------ ----------- ---------- ------------ ----------- ---------- ------------ ----------- ----------
a1 .2000 1 001 .8000 5 002 NULL NULL NULL
a2 .5000 32 004 .3000 2 005 .2000 54 007*/
insert @t
select 'a1',0.2,1,'001' union all
select 'a1',0.8,5,'002' union all
select 'a2',0.3,2,'005' union all
select 'a2',0.2,54,'007' union all
select 'a2',0.5,32,'004'select cuno,
max(case rid when 0 then [percent] end) as percent1,
max(case rid when 0 then [empl_name] end) as empl_name1,
max(case rid when 0 then [empl_dept] end) as empl_dept1,
max(case rid when 1 then [percent] end) as percent2,
max(case rid when 1 then [empl_name] end) as empl_name2,
max(case rid when 1 then [empl_dept] end) as empl_dept2,
max(case rid when 2 then [percent] end) as percent3,
max(case rid when 2 then [empl_name] end) as empl_name3,
max(case rid when 2 then [empl_dept] end) as empl_dept3
from (
select *,
(select count(*) from @t where cuno = a.cuno and empl_dept < a.empl_dept) as rid
from @t a) aa
group by cuno/*cuno percent1 empl_name1 empl_dept1 percent2 empl_name2 empl_dept2 percent3 empl_name3 empl_dept3
---- -------- ----------- ---------- --------- ----------- ---------- --------- ----------- ----------
a1 .2000 1 001 .8000 5 002 NULL NULL NULL
a2 .5000 32 004 .3000 2 005 .2000 54 007*/
insert @t
select 'a1',0.2,1,'001' union all
select 'a1',0.8,5,'002' union all
select 'a2',0.3,2,'005' union all
select 'a2',0.2,54,'007' union all
select 'a2',0.5,32,'004'
select identity(int,1,1) as ID,a.* into #t from @t a order by cuno--给原来的表多加了一个标志列,再插入临时表
declare @i varchar(10),@s varchar(8000)
select top 1 @s='',@i=count(*)--只取一条记录,那么@i=2
from #t group by cuno order by count(*) desc
while @i>0
select @i=@i-1,
@s=',percent'+@i+'=max(case a.id-b.id when '+@i+' then a.[percent] else 0 end),
empl_name'+@i+'=max(case a.id-b.id when '+@i+' then a.empl_name else 0 end),
empl_dept'+@i+'=max(case a.id-b.id when '+@i+' then a.empl_dept else 0 end)'+@s
exec('
select a.cuno'+@s+'
from #t a,(select cuno,id=min(id) from #t group by cuno)b
where a.cuno=b.cuno
group by a.cuno')
drop table #t
go