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.

Please confirm whether your table has only `Column1` or has each of the columns in question. If it does have all three columns then the solution is trivial (see the answer @BonyT posted) and does not require the use of CTE. — Yuck, Jun 17 '11 at 13:57
Table can contain all those columns before calculating. But I can not solve it without CTE, shame on me. I'll try to find that, thanks — Dok, Jun 18 '11 at 02:57

score 1 · Answer 1 · answered Jun 20 '11 at 07:24

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

answered Jun 20 '11 at 07:24

Andreas

83
5

It really worked, I appreciate it. But kind of slow. Took 1 minute more on real table which has more than 10000 records. – Dok Jun 20 '11 at 14:29
After reducing the decimal places of float, it worked much better. Thank you dude :-) – Dok Jun 20 '11 at 17:59
Sure, I can. But I don't know how to do it, I mean I can not see anything to mark. – Dok Sep 14 '11 at 09:21

score 0 · Answer 2 · answered Jun 17 '11 at 12:41

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.

BonyT · Answer 3 · 2011-06-17T13:26:45.870

0

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).

edited Jun 17 '11 at 13:26

answered Jun 17 '11 at 12:59

BonyT

10,750
5
31
52

I think the OP is using existing data, and not building the table from `INSERT`. – Yuck Jun 17 '11 at 13:42
You have access to current existing data within a INSTEAD OF trigger as well as inserted data. Only if you need historical data older than the current, do you need a Recursive CTE. As I have shown above, the values he requires can be calculated on the basis of the current values alone. – BonyT Jun 17 '11 at 13:48
@BonyT: Yes, your answer works if the table actually *has* all of those columns. What if his table only has `Column1`? Then you **must** use a CTE to calculate the results. From the question, `Column1` is the only one which is not being calculated from some other value. – Yuck Jun 17 '11 at 13:55
Adding the two columns to the table is far easier and performant than creating a CTE. You still need to store the historical data - just a CTE would end up storing far more than required. – BonyT Jun 17 '11 at 14:01
@BonyT: I totally agree. Read my comment on the question to @Dok. If adding columns is possible or already done, then yes, your answer is correct and I will +1. However, he has explicitly asked for CTE which leads me to believe he needs a recursive solution. See the related question he mentioned above. – Yuck Jun 17 '11 at 14:03
I see - his previous question indicated no triggers, so as you say he may be specifically looking for an alternate solution. – BonyT Jun 17 '11 at 14:09
Thank you guys. As I mentioned before, I am very fresh. So I thought, CTE is the only way to get the previous record data. That's why I am stuck with that. Actually, Column1 is the only one has existing data. And I created those column2, column3 in a table already to put the new data. I got like, CTE collects all data I need, then I should put them into the table. So, what is the next way? Where should I go instead of using CTE? – Dok Jun 18 '11 at 03:04

Calculate multiple columns with each other using CTE

3 Answers3