How to add dynamic Columns in SQL?

Question

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?

There is logical processing of query, basicaly you can use new column names just in Order by statement msdn.microsoft.com/en-us/library/ms189499.aspx — Justin
– Justin, Commented May 6, 2014 at 6:36

Dmitrii Bychenko · Accepted Answer · 2014-05-06 06:31:03Z

3

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

answered May 6, 2014 at 6:31

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dmitrii Bychenko Over a year ago

@Nikhil Agrawal: it has name CTE in MS SQL, but "with" works (with some restrictions) in Oracle, Postgre as well

Kahn · Accepted Answer · 2014-05-06 06:30:17Z

0

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

answered May 6, 2014 at 6:30

Kahn

1,6601 gold badge13 silver badges23 bronze badges

Comments

adrianm · Accepted Answer · 2014-05-06 06:34:29Z

0

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

answered May 6, 2014 at 6:34

adrianm

14.8k6 gold badges60 silver badges110 bronze badges

Collectives™ on Stack Overflow

How to add dynamic Columns in SQL?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related