Deriving new column using new column

Question

Normally we can build new columns in Select statements. Is it possible to derive new columns out of new columns in Select statements?

For example,

Select 1+2 A

This gives us a table with column A and the value 3 in 1 row.

Can I build a new column B that is based on A?

For example,

Select 1+2 A, 4+A B

Unfortunately the above will result in "Invalid column name A".

You can't. You can wrap query 1 up in a derived table, and then add the B column. — jarlh, Oct 02 '19 at 14:27
so does it mean that for every additional new derived column, I have to build a derived table first? — Victor Ng, Oct 02 '19 at 14:31
My base table has a large number of records (10 million) and the requirement is to add 5 new columns which are derived from previously derived columns. I originally intend to build a view with a single create statement such that all new columns can be declared together. — Victor Ng, Oct 02 '19 at 14:41
You don't need multiple create statements because the outer query and the subquery are part of your select statement — Radagast, Oct 02 '19 at 14:46
Wouldn't it become a performance issue when i wrap queries within queries on large tables? — Victor Ng, Oct 02 '19 at 15:04
@VictorNg . . . Generally no performance issue but you should tag your question with the database you are using. — Gordon Linoff, Oct 02 '19 at 17:00

score 1 · Accepted Answer · answered Oct 02 '19 at 15:54

1

Use a subquery.

select 4 + A as 'B'
from (
    select 1 + 2 as 'A'
) q

answered Oct 02 '19 at 15:54

dougp

score 1 · Answer 2 · answered Oct 02 '19 at 20:55

1

Use a temporary table

Select 1+2 as A
INTO #TEMPORARY_TABLE
FROM TABLE

SELECT 4+A as B
FROM #TEMPORARY_TABLE

answered Oct 02 '19 at 20:55

Ivancito

2 Answers2