CODEWITHSUNDEEP
sql-servert-sql
Eduardo Maia
Eduardo Maia

Reputation: 617

How to reuse calculated columns avoiding duplicating the sql statement

I have a lots of calculated columns and they keep repeating 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

Upvotes: 43

Views: 25906

Answers (6)

Anders Abel
Anders Abel

Reputation: 69260

Do you want calculated results out of your table? In that case you can put the relevant calculations in scalar valued user defined function and use that inside your select statement.

Or do you want the calculated results to appear as columns in the table, then use a computed column:

CREATE TABLE Test(
    ID INT NOT NULL IDENTITY(1,1),
    TimesTen AS ID * 10
)

Upvotes: 0

Elastep
Elastep

Reputation: 3408

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

Upvotes: 9

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

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

Upvotes: 10

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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)

Upvotes: 35

Bobby D
Bobby D

Reputation: 2159

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.

Upvotes: 1

Oscar Gomez
Oscar Gomez

Reputation: 18488

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.

Upvotes: 1

Related Questions