Adding new columns to a result set.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Adding new columns to a result set.

Author

Topic

frank2
Starting Member

35 Posts

Posted - 2005-08-23 : 10:58:56

I have a SELECT in which I want to add new columns to the result set. Some columns will be calculated from other columns in the record. Others I want to simply specify the column data type and size to be populated later.

I found this example which adds an Identity column.

Select Identity(int,1,1) ID, au_lname, au_fname into #Temp from authors

But I have not been able to find any documentation on how to add calculated columns or create columns in a SELECT.

Can some one tell me how to do it or point me to some good documentation?

Thanks

mfemenel
Professor Frink

1421 Posts

Posted - 2005-08-23 : 11:12:39

Try this. Select intcolumn1,intcolumn2,intcolumn1+intcolumn2 as intsumcolumn,'' as emptycolumn from tablename.

You really just specify your formulas and give the column a name, or you can just use an empty string and give it a name for a text column.

Mike
"oh, that monkey is going to pay"

frank2
Starting Member

35 Posts

Posted - 2005-08-23 : 11:24:40

That is very helpfull. How would I create a new int or varchar column?

mfemenel
Professor Frink

1421 Posts

Posted - 2005-08-23 : 11:46:58

varchar should be fairly easy, select cast('' as varchar(20)). For an int column, you may need to do it in two steps select cast(0 as int) from.... then if you want to, go back and update the 0's to null or leave 0 as your default value.

Mike
"oh, that monkey is going to pay"

frank2
Starting Member

35 Posts

Posted - 2005-08-23 : 15:25:13

I will play with the technique that you suggested. Looks like that will do it for me. Thanks.

I was wondering in this SELECT statement I thought the identity column was being added to the result table but does not exist in the source table? Is this a technique that can be used with out casting?

Select Identity(int,1,1) ID, au_lname, au_fname into #Temp from authors

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-24 : 01:26:05

Are you trying to copy data with caluculated column to other table or just want to show it as a seperate column in the source table itself?

Madhivanan

Failing to plan is Planning to fail

frank2
Starting Member

35 Posts

Posted - 2005-08-24 : 10:14:23

Hopefully this will shed a little more light on what i am trying to accomplish. Given table MyTable which has two columns MT1 and MT2. I want to create a result table MyResult consisting of MT1, MT2 and a new column MR1 of type varchar or int that is added by the SELECT.

SELECT MT1, MT2, <some code to create MR1 of type varchar>
INTO MyResult
FROM MyTable

I don’t know if this is even possible in SQL 2000. I am coming from Visual FoxPro where this capability exists.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-24 : 10:27:42

It depends on the datatype of the column of source table
Why do you want to create new datatype?

Madhivanan

Failing to plan is Planning to fail

frank2
Starting Member

35 Posts

Posted - 2005-08-24 : 10:55:41

I need to pull columns from MyTable that I need in MyResult. MyResult also needs to have additional columns that will be populated later. I did not want to have to use an alter table after creating the result set to add the needed fields if I could do it in the select when the result set is created.

Also, I have a function that returns a table type. But I need some additional columns added to the result table returned that will be populated by the calling block. I would like to simply modify the select statement used in the function to add the desired columns.

We are moving from Visual FoxPro to SQL 2000. In the existing application there are a lot of instances where a select adds empty or pre loaded columns to the result set. (usually of int or varchar type) I would like to replicate this in SQL.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-24 : 11:04:56

Well

You can use this

SELECT MT1, MT2, 'a' as MR1 INTO MyResult FROM MyTable

Now a column MR1 with Varchar datatype will be created (If you want to increase the width say 5 then use 'aaaaa'

Madhivanan

Failing to plan is Planning to fail

frank2
Starting Member

35 Posts

Posted - 2005-08-24 : 11:51:00

Thanks. That solves it for me. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources