How to reuse calculated columns avoiding duplicating the sql statement?

Question

I have a lots of calculated columns and they keep repeting themselves, one inside of the others, including nested cases statements.

There is a really simplified version of something that i've searching a way to do.

SELECT 
    (1+2) AS A, 
    A + 3 AS B, 
    B * 7 AS C 
FROM MYTABLE

Thanks in advance.

Answer 1

You could try something like this.

SELECT 
    A.Val AS A, 
    B.Val AS B, 
    C.Val AS C 
FROM MYTABLE
  cross apply(select 1 + 2) as A(Val)
  cross apply(select A.Val + 3) as B(Val)
  cross apply(select B.Val * 7) as C(Val)

Answer 2

You can't reference just-created expressions by later referencing their column aliases. Think of the entire select list as being materialized at the same time or in random order - A doesn't exist yet when you're trying to make an expression to create B. You need to repeat the expressions - I don't think you'll be able to make "simpler" computed columns without repeating them, and views the same - you'll have to nest things, like:

SELECT A, B, C = B * 7
FROM
(
  SELECT A, B = A + 3
  FROM 
  (
    SELECT A = (1 + 2)
  ) AS x
) AS y;

Or repeat the expression (but I guess that is what you're trying to avoid).

Answer 3

Another option if someone is still interested:

with aa(a) as ( select 1+2 ) 
, bb(b) as ( select a+3 from aa ) 
,cc(c) as ( select b*7 from bb) 
SELECT aa.a, bb.b, cc.c 
from aa,bb,cc

Answer 4

The only way to "save" the results of your calculations would be using them in a subquery, that way you can use A, B and C. Unfortunately it cannot be done any other way.