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.

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

Solution 2

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

Solution 3

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

Solution 4

You can create computed columns to represent the values you want. Also, you can use a view if your calculations are dependent on data in a separate table.

Solution 5

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.

View more solutions

Comments

[Siddhartha Gandhi]

So this works when you are cross applying a single row of data. What if I want to re-use a window function value? For instance, something like this: C.Val / SUM(C.Val) OVER()

[Mikael Eriksson]

@Sid you need a derived table or a CTE where you return the values for all columns needed plus the window function and then in the main query you can do calculations in cross apply using the columns from the derived table.