从表A转换到表B

从表A转换到表B;用SQL实现

Table A:
　　
　 col1　　
a　
b　
c　
d　
e



Table B:　　
col1 col2
a (a+b)/2
a+b (a+b+c)/3
a+b+c (b+c+d)/3
a+b+c+d (c+d+e)/3
a+b+c+d+e(d+e)/2


[解决办法]
就5行数据?

SQL code

SET NOCOUNT ONcreate TAble A (col1 float, col2 float)insert A (col1)select 1union all select 2union all select 3union all select 4union all select 5GOWith Ac (rowID, col1, col2)AS(    Select row_number() over(order by Current_TimeStamp) as rowID, col1, col2    From A)select (select sum(col1) from Ac where rowID <= Ac1.rowID) as col1,      CASE WHEN rowid= 1 THEN (select sum(col1)/2 from AC where rowID <=2)        WHEN rowID = (select max(rowID) from Ac) THEN (select sum(col1)/2 from AC where        rowID >= Ac1.rowID-1)        ELSE (select sum(col1/3) from AC where rowID >= Ac1.rowID-1 AND rowID <= Ac1.rowID+1)    END as col2FROM Ac Ac1drop table A
[解决办法]
不是很理解楼主的意思
[解决办法]
是计算值还是显示字符？是计算：
create table A(col1 int)
insert A select 3  
insert A select 2  
insert A select 5  
insert A select 1  
insert A select 4
-----------------------
select *,identity(int,1,1) Cid into #B from A--借用临时表
select col1,(select sum(col1) from #B b where b.cid<=a.cid)累计和,
(select avg(col1*1.0) from #B c where abs(c.cid-a.cid)<=1)前后平均数 from #B a  
------------------------------------
col1        累计和         前后平均数                                   
----------- ----------- ----------------------------------------  
3           3           2.500000
2           5           3.333333
5           10          2.666666
1           11          3.333333
4           15          2.500000

（所影响的行数为 5 行）