CODEWITHSUNDEEP
sqlsql-server
Nikhil Agrawal
Nikhil Agrawal

Reputation: 48558

How to add dynamic Columns in SQL?

I am using following query to do this

select (Col_A + Col_B) Foo,
       (Col_C + Col_D) Bar,
       (Col_A + Col_B + Col_C + Col_D) FooBar from Table1

But is there a way like to do like this

select (Col_A + Col_B) Foo,
       (Col_C + Col_D) Bar,
       (Foo + Bar) FooBar from Table1

it gives Error

'Invalid Column Foo'
'Invalid Column Bar'

How to solve this?

Upvotes: 2

Views: 75

Answers (3)

adrianm
adrianm

Reputation: 14726

SQL parses the FROM before SELECT so you need to add your alias in your FROM clause

SELECT Foo
      ,Bar
      ,Foo + Bar
FROM Table1
     CROSS APPLY (
         SELECT Col_A + Col_B AS Foo
               ,Col_C + Col_D AS Bar
     ) AS CA1

Upvotes: 0

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186668

Partial solution is to use with construction:

with query as (
  select (Col_A + Col_B) Foo,
         (Col_C + Col_D) Bar
    from Table1)

select Foo,
       Bar,
       Foo + Bar
  from query

Upvotes: 3

Kahn
Kahn

Reputation: 1660

Afaik those columns you're referring to don't exist by those names yet.

You can of course try the following instead:

SELECT *, (Foo + Bar) FooBar
FROM (select (Col_A + Col_B) Foo,
       (Col_C + Col_D) Bar
     from Table1) SRC

Upvotes: 0

Related Questions