How would you do to transform a Column in a table from this:

``ColumnA ColumnB2 a3 b4 c5 d1 a``

to this:

``ColumnA ColumnB3 a6(=3+3) b10(=4+3+3) c15(=5+4+3+3) d``

I'm interested to see esp. what method you would pick.

Like this:

``````;WITH cte
AS
(
SELECT ColumnB, SUM(ColumnA) asum
FROM @t
gROUP BY ColumnB

), cteRanked AS
(
SELECT asum, ColumnB, ROW_NUMBER() OVER(ORDER BY ColumnB) rownum
FROM cte
)
SELECT (SELECT SUM(asum) FROM cteRanked c2 WHERE c2.rownum <= c1.rownum),
ColumnB
FROM cteRanked c1;
``````

This should give you:

``````ColumnA    ColumnB
3             a
6             b
10            c
15            d
``````

## Here is a live demo

I'd generally avoid trying to do so, but the following matches what you've asked for:

``````declare @T table (ColumnA int,ColumnB char(1))
insert into @T(ColumnA,ColumnB) values
(2    ,       'a'),
(3   ,        'b'),
(4  ,         'c'),
(5 ,          'd'),
(1,           'a')

;With Bs as (
select distinct ColumnB from @T
)
select
SUM(t.ColumnA),b.ColumnB
from
Bs b
inner join
@T t
on
b.ColumnB >= t.ColumnB
group by
b.ColumnB
``````

Result:

``````            ColumnB
----------- -------
3           a
6           b
10          c
15          d
``````

For small data sets, this will be fine. But for larger data sets, note that the last row of the table relies on obtaining the `SUM` over the entire contents of the original table.

Not sure if this is optimal, but how about (SQL Fiddle):

``````SELECT x.A + COALESCE(SUM(y.A),0) ColumnA, x.ColumnB
FROM
(
SELECT SUM(ColumnA) A, ColumnB
FROM myTable
GROUP BY ColumnB
) x
LEFT OUTER JOIN
(
SELECT SUM(ColumnA) A, ColumnB
FROM myTable
GROUP BY ColumnB
) y ON y.ColumnB < x.ColumnB
GROUP BY x.ColumnB, x.A
``````
``````create table #T
(
ID int primary key,
ColumnA int,
ColumnB char(1)
);

insert into #T
select row_number() over(order by ColumnB),
sum(ColumnA) as ColumnA,
ColumnB
from YourTable
group by ColumnB;

with C as
(
select ID,
ColumnA,
ColumnB
from #T
where ID = 1
union all
select T.ID,
T.ColumnA + C.ColumnA,
T.ColumnB
from #T as T
inner join C
on T.ID = C.ID + 1
)
select ColumnA,
ColumnB
from C
option (maxrecursion 0);

drop table #T;
``````

You can use below simple select statement for the same

``````SELECT COLUMN_A, COLUMN_B,
(SELECT SUM(COLUMN_B) FROM #TBL T2 WHERE T2.ID  <= T1.ID) as SumofPreviousRow FROM #TBL T1;
``````

Top