-- had tested,pls try:select id,ttbid,(select count(id) from table1 where id<T.id and ttbid=T.ttbid ) /3 as tid ,jin_e
into #T
from table1 Tselect ttbid,tid,sum(jin_e) as tot
into #T2 from #T group by ttbid,tid order by ttbid,tidselect ttbid, isnull((select tot from #T2 where ttbid=TT.ttbid and tid=0),0) as jin_1
, isnull((select tot from #T2 where ttbid=TT.ttbid and tid=1),0) as jin_2
, isnull((select tot from #T2 where ttbid=TT.ttbid and tid=2),0) as jin_3
, isnull((select tot from #T2 where ttbid=TT.ttbid and tid=3),0) as jin_4
from
(select distinct ttbid from #T2) TT
into #T
from table1 Tselect ttbid,tid,sum(jin_e) as tot
into #T2 from #T group by ttbid,tid order by ttbid,tidselect ttbid, isnull((select tot from #T2 where ttbid=TT.ttbid and tid=0),0) as jin_1
, isnull((select tot from #T2 where ttbid=TT.ttbid and tid=1),0) as jin_2
, isnull((select tot from #T2 where ttbid=TT.ttbid and tid=2),0) as jin_3
, isnull((select tot from #T2 where ttbid=TT.ttbid and tid=3),0) as jin_4
from
(select distinct ttbid from #T2) TT
isnull(X.jin_e_total,0) jin_e_total1,
isnull(Y.jin_e_total,0) jin_e_total2,
isnull(Z.jin_e_total,0) jin_e_total3,
isnull(R.jin_e_total, 0) jin_e_total4 from
(select ttbid, sum(A.jin_e) as jin_e_total from table1 as A where
(select count(id) from table1 where id <= A.id and ttbid = A.ttbid)
between 1 and 3
group by ttbid ) as X left join
(select ttbid, sum(A.jin_e) as jin_e_total from table1 as A where
(select count(id) from table1 where id <= A.id and ttbid = A.ttbid)
between 4 and 6
group by ttbid ) as Y
on X.ttbid = Y.ttbid left join
(select ttbid, sum(A.jin_e) as jin_e_total from table1 as A where
(select count(id) from table1 where id <= A.id and ttbid = A.ttbid)
between 7 and 9
group by ttbid ) as Z
on X.ttbid = Z.ttbid left join
(select ttbid, sum(A.jin_e) as jin_e_total from table1 as A where
(select count(id) from table1 where id <= A.id and ttbid = A.ttbid)
between 10 and 12
group by ttbid ) as R
on X.ttbid = R.ttbidttbid jin_e_total1 jin_e_total2 jin_e_total3 jin_e_total4
----------- ------------ ------------ ------------ ------------
234 800 300 0 0
235 500 0 0 0
236 200 0 0 0
237 600 1300 300 0(4 row(s) affected)
insert into @table1 values (1,234,200);
insert into @table1 values (2,234,500);
insert into @table1 values (3,234,100);
insert into @table1 values (4,234,300);
insert into @table1 values (5,235,300);
insert into @table1 values (6,235,200);
insert into @table1 values (7,236,200);
insert into @table1 values (8,237,100);
insert into @table1 values (9,237,200);
insert into @table1 values (10,237,300);
insert into @table1 values (11,237,500);
insert into @table1 values (12,237,600);
insert into @table1 values (13,237,200);
insert into @table1 values (14,237,300);
select * from @table1
declare @tempid int,@tempttbid int,@tempjin int,@num int
declare @table2 table(ttbid int,jin1 int,jin2 int,jin3 int,jin4 int)
declare @jin1 int,@jin2 int,@jin3 int,@jin4 int
select @jin1=0,@jin2=0,@jin3=0,@jin4=0
select @num=1
declare MyCursor CURSOR
global scroll static
for select * from @table1
open MyCursor
declare @ID int
declare @ttbid int
declare @jin int
fetch next from MyCursor
into @tempid,@tempttbid,@tempjin select @jin1=@tempjin
fetch next from MyCursor
into @ID,@ttbid,@jin while(@@fetch_status=0)
begin
if(@tempttbid<>@ttbid)
begin
insert into @table2 values (@tempttbid,@jin1,@jin2,@jin3,@jin4)
select @jin1=@jin,@jin2=0,@jin3=0,@jin4=0
select @tempttbid=@ttbid
select @num=1
end
else
begin
select @num=@num+1
if(@num<=3)
select @jin1=@jin1+@jin
if(@num>3 and @num<=6)
select @jin2=@jin2+@jin
if(@num>6 and @num<=9)
select @jin3=@jin3+@jin
if(@num>9 and @num<=12)
select @jin4=@jin4+@jin
end
fetch next from MyCursor
into @ID,@ttbid,@jin
end close MyCursor
deallocate MyCursor
insert into @table2 values (@tempttbid,@jin1,@jin2,@jin3,@jin4)select * from @table2
id int,
ttbid int,
jin_e int);insert into table1 values (1,234,200);
insert into table1 values (2,234,500);
insert into table1 values (3,234,100);
insert into table1 values (4,234,300);
insert into table1 values (5,235,300);
insert into table1 values (6,235,200);
insert into table1 values (7,236,200);
insert into table1 values (8,237,100);
insert into table1 values (9,237,200);
insert into table1 values (10,237,300);
insert into table1 values (11,237,500);
insert into table1 values (12,237,600);
insert into table1 values (13,237,200);
insert into table1 values (14,237,300);select *,0 flag into #temp from table1
declare @a int,@b int
update #temp set @a=case when @b=ttbid then @a+1 else 0 end,flag=@a,@b=ttbidselect ttbid,
sum(case when flag/3=0 then jin_e else 0 end) jin_1,
sum(case when flag/3=1 then jin_e else 0 end) jin_2,
sum(case when flag/3=2 then jin_e else 0 end) jin_3,
sum(case when flag/3=3 then jin_e else 0 end) jin_4
from #temp group by ttbid order by ttbiddrop table #temp
drop table table1