6

How can I use a calculated column multiple times in the same select without repeating the expression and without using common table expressions or complex subselects?

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 any way to solve this in SQL Server 2005?

Jarrod Dixon
  • 15,727
  • 9
  • 60
  • 72
arturn
  • 725
  • 2
  • 11
  • 25

2 Answers2

12

You can use cross apply

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

or a CTE

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

or a subquery (also known as a derived table)

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

It is not possible to use the column alias in the same field list as it is declared.

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • The cross apply approach is amazing!!!! One question though - is it effective to use resource-wise? Does the interpreter handle it well? – GeorgiG Aug 28 '18 at 12:22
  • 1
    @GeorgiG If you want to know for sure you should have a look at the query plan to see how it is executed. In this simple case the `cross apply` is expanded to calculate three different values in one Compute Scalar operator which is exactly the same as the other two versions does and the same as if you had duplicated the code in the column list. So the cross apply is only syntactic sugar in this case making you type less and making the code easier to understand and maintain. – Mikael Eriksson Aug 28 '18 at 12:40
  • Thanks a lot. I saw it created a Compute Scalar, but failed to check if the original versions did the same. Anyway, great info! Thanks. – GeorgiG Aug 29 '18 at 08:52
1

You could use a derived table to accomplish this:

SELECT 
     *,
     [ADD]*2,
     [ADD]/2
FROM
(
     SELECT (NUM1+NUM2) AS [ADD], NUM1, NUM2 FROM @T 
) AS A
Doozer Blake
  • 7,677
  • 2
  • 29
  • 40