Remove duplication in SELECT statement

Question

Say I have the following SQL:

SELECT 
 amount,
 amount*.1,
 (amount*1)+3,
 ((amount*1)+3)/2,
 (((amount*1)+3)/2)+37
FROM table

Instead of repeating that identical code every time, I really want to be able to do something like this:

SELECT
 amount,
 amount*.1 AS A,
 A+3 AS B,
 B/2 AS C,
 C+37 AS D,
FROM table

But this code doesn't work.

So, is there another way to avoid duplication in the working query that I have?

• rolfl: Not with T-SQL - From: technet.microsoft.com/en-us/library/ms176104.aspx ` < select_list > The columns to be selected for the result set.` and A, B, and C are not columns.
• rolfl: FYI: stackoverflow.com/questions/12988637/column-aliases-reference
• rolfl: Is something like this possible? and the 'this' code does not work. If you are specifically concerned that it was closed 'because it was asking for code to be written', well, it is also required that the code-to-be-reviewed also works: See question 5 here: codereview.stackexchange.com/help/on-topic
• Dan Lyons: How are the results from this query used? The calculations look like business logic which belong in a domain object rather than inserted directly into a query. I would only put them in the query if I had no other choice (e.g., building reports).
• brainbolt: In this case, I'm just generating tablular data to be imported into a separate system. There is no domain object. Otherwise, I agree with you. I wouldn't normally want to do this kind of stuf in SQL.

Answer 1

That's not how SQL works. As mentioned in the SO answer linked by @rolfl:

You can only refer to a column alias in an outer select, so unless you recalculate all the previous values for each column you'd need to nest each level, which is a bit ugly

Which means:

SELECT amount, A, B, C, C+37 D
FROM (SELECT amount, A, B, B/2 C
 FROM (SELECT amount, A, A+3 B
 FROM (SELECT amount, amount*0.1 A FROM table) firstPass) secondPass) thirdPass

That said, the last comma in your 2^nd snippet is a pseudo syntax error (given it's a pseudo query)

Answer 2

You could define intermediate views using common table expressions. That would eliminate the redundancy of the calculations, but redundantly introduce a different kind of redundancy.

WITH ATable AS (
 SELECT amount, amount*.1 AS A FROM table
), BTable AS (
 SELECT amount, A, A+3 AS B FROM ATable
), CTable AS (
 SELECT amount, A, B, B/2 AS C FROM BTable
)
SELECT amount, A, B, C, C+37 AS D FROM CTable;

• svick: "redundantly introduce a different kind of redundancy." Isn't that "redundantly" redundant?
• Malachi: you said something about the redundancy in your answer and then redundantly commented ""redundantly introduce a different kind of redundancy." Isn't that "redundantly" redundant?" on the Redundancy of the statement.

Answer 3

If this is SQL Server 2005 or later version, you could use CROSS APPLY:

SELECT
 t.amount,
 a.A,
 b.B,
 c.C,
 d.D
FROM atable AS t
CROSS APPLY (SELECT t.amount*.1 AS A) AS a
CROSS APPLY (SELECT a.A+3 AS B) AS b
CROSS APPLY (SELECT b.B/2 AS C) AS c
CROSS APPLY (SELECT c.C+37 AS D) AS d
;

Essentially, every CROSS APPLY here creates a calculated column which can be referenced in subsequent CROSS APPLYs just as well as in the SELECT clause, i.e. this way you are creating re-usable calculated columns.

If this is an older version or Sybase, there's probably no other option apart from the one suggested by @retailcoder.