Calculate multiple columns with each other using CTE

Question

I want to build columns that calculated with each other. (Excuse my English) Example:

Id   Column1    Column2                                                           Column3
1    5          5 => Same as Column1                                              5 => Same as Column2
2    2          12 => column1 current + column2.prev + column3.previous = 2+5+5   17 => column2.current + column3.prev = 12+5
3    3          32 => 3+12+17                                                     49 => 32+17

easier way to see:

Id   Column1    Column2                  Column3
1    5          5 => Same as Column1     5 => Same as Column2
2    2          12 =>   2+5+5            17 => 12+5
3    3          32 =>   3+12+17          49 => 32+17

so complicated??? :-(

The previous issue was calculating Column3 with the new calculated column as Column2. But now, it must be renew with the just calculated Column2 and the previous record of Column3 as well. If you want to have a look at the previous post, here it is.

---

Here is my previous recursive CTE code. It works like, 1st, calculate column2 with previous record of current column (c.Column2) in cteCalculation, and then calculate new column3 in cte2 with just calculated column2 from cteCalculation.

/copied from that previous post/

;with cteCalculation as (
    select t.Id, t.Column1, t.Column1 as Column2
        from table_1 t
        where t.Id = 1
    union all
    select t.Id, t.Column1, (t.Column1 + c.Column2) as Column2
        from table_1 t
            inner join cteCalculation c
                on t.Id-1 = c.id
),
cte2 as(
select t.Id, t.Column1 as Column3
        from table_1 t
        where t.Id = 1
    union all
    select t.Id, (select column2+1 from cteCalculation c where c.id = t.id)  as Column3
        from table_1 t
            inner join cte2 c2
                on t.Id-1 = c2.id
)

select c.Id, c.Column1, c.Column2, c2.column3
    from cteCalculation c
inner join cte2 c2 on c.id = c2. id

Now I wanna extend it like calculate 2 columns with the data from each other. Means, use 2nd to calc the 3rd, and use 3rd to get new 2nd column data. Hope you can get it.

Answer 1

This is an example how to achive this using recursive CTE

create table #tmp (id int identity (1,1), Column1 int)
insert into #tmp values(5)
insert into #tmp values(2)
insert into #tmp values(3);

with counter as
(
    SELECT top 1 id, Column1, Column1 as Column2, Column1 as Column3 from #tmp
    UNION ALL 
    SELECT t.id, t.Column1, 
           t.Column1 + counter.Column2 + counter.Column3, 
           (t.Column1 + counter.Column2 + counter.Column3) + counter.Column3 FROM counter
    INNER JOIN #tmp t ON t.id =  counter.id + 1
)

select * from counter

Answer 2

You'll need to use a Recursive CTE since the values of subsequent columns are dependent upon earlier results.

Do this in pieces, too. Have your first query just return the correct values for Column1. Your next (recursive CTE) query will add the results for Column2, and so on.

Answer 3

OK I'm assuming you're doing inserts into column 1 here of various values.

Essentially col2 always = new col1 value + old col2 value + old col 3 value col3 = new col2 value + old col3 value so col3 = (new col1 value + old col2 value + old col 3 value) + old col3 value

So an INSTEAD OF Insert trigger is probably the easiest way to implement.

 CREATE TRIGGER tr_xxxxx ON Tablename

 INSTEAD OF INSERT

 AS

 INSERT INTO Tablename (Column1, Column2, Column3)
 SELECT ins.col1, ins.col1+t.col2+t.col3, ins.col1+t.col2+t.col3+t.col3
 FROM Tablename t INNER JOIN Inserted ins on t.Id = ins.Id

The trigger has access to both the existing (old) values in Tablename t, and the new value being inserted (Inserted.col1).