How can I avoid repeating a computed expression several times in the same element?

Question

How can I use a calculated column several times in the same element without repeating the expression and without using common table expressions or complex sub-selects?

DECLARE @T TABLE ( NUM1 INT,NUM2 INT) INSERT INTO @T VALUES (2,3); INSERT INTO @T VALUES (5,7); INSERT INTO @T VALUES(32,3); INSERT INTO @T VALUES(6,8); SELECT (NUM1+NUM2) [ADD], [ADD]*2, [ADD]/2,* FROM @T 

Is there a way to solve this problem in SQL Server 2005?

Answer 1

You can use cross aplly

 SELECT T2.[ADD], T2.[ADD]*2, T2.[ADD]/2 FROM @T AS T1 CROSS APPLY (SELECT T1.NUM1+T1.NUM2) AS T2([ADD]) 

or cte

 ;WITH C AS ( SELECT NUM1+NUM2 AS [ADD] FROM @T ) SELECT [ADD], [ADD]*2, [ADD]/2 FROM C 

or subquery (also known as a view)

 SELECT T.[ADD], T.[ADD]*2, T.[ADD]/2 FROM ( SELECT NUM1+NUM2 AS [ADD] FROM @T ) AS T 

Cannot use a column alias in the same field list as declared.

Answer 2

You can use a view to do this:

 SELECT *, [ADD]*2, [ADD]/2 FROM ( SELECT (NUM1+NUM2) AS [ADD], NUM1, NUM2 FROM @T ) AS A