Adding column in a select statement

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 column in a select statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-11 : 06:53:19

Leif writes "Hi,

I'm trying to add a column to my SELECT statement. I'm halfway there by using SPACE(50) but the other half is populating all the entries in that column and that's where I'm lost.

Thanks very much in advance

Leif"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-11 : 07:03:49

Show us what you have got so far.

Peter Larsson
Helsingborg, Sweden

Leiner
Starting Member

3 Posts

Posted - 2007-01-11 : 07:41:16

I've got something like this :

SELECT user AS Name,
SPACE(20)AS Status
FROM Members

The space (20) creates a colomn but I'm also trying to populate the rows in that column with a default value.

Cheers

Leif

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-11 : 07:46:18

If there is a default value, it is stored when the records is inserted. If it is not there anymore, it means that someone has updated the record with a new value for the column.

You shouldn't rely on selecting the default value.

Peter Larsson
Helsingborg, Sweden

Leiner
Starting Member

3 Posts

Posted - 2007-01-11 : 07:55:34

Maybe I'm not explaining this properly but the record does not exist in the database as such so what I'm trying to do is to add a column (SPACE(20) and then populate the rows with a value that I determine (could be anything, it's not part of the DB either)

Cheers
Leif

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-11 : 08:01:50

ALTER TABLE <YourTableNameHere> ADD COLUMN <YourColumnNameHere> VARCHAR(20)
GO
UPDATE <YourTableNameHere> SET <YourColumnNameHere> = space(20)

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-11 : 08:02:50

If all you want is create a column and populate with some default value on the creation itself, why don't you use that default value instead of SPACE(50) while creating column

For example, say you want default value 'N/A', you can use:

SELECT user AS Name,
CONVERT(VARCHAR(50), 'N/A') AS Status
FROM Members

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-11 : 08:07:03

Or is it

SELECT ISNULL(<YourColumnNameHere>, '#n/a') from <YourTableNameHere>

??? This replaces a NULL value for a column in every record with some value of your choice.

Peter Larsson
Helsingborg, Sweden

Leiner
Starting Member

3 Posts

Posted - 2007-01-11 : 08:15:43

That's it CONVERT(VARCHAR(50), 'N/A') AS Status was what I was looking for.
Many thanks to both of you for your help and patience.
Leif

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-11 : 10:14:40

???

you can just write

SELECT user as Name, 'NA' as Status
from ...

no need for converts....

- Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-11 : 10:25:34

Maybe he wants this in a VIEW for exporting later to a fixed length file?

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-11 : 10:25:38

No, if you remove CONVERT part you will get column of size 2 only not 50, which OP wants.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources