adding a column to a table depends on other column

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 2005 Forums

Transact-SQL (2005)

adding a column to a table depends on other column

Author

Topic

Exir
Posting Yak Master

151 Posts

Posted - 2009-02-01 : 06:51:52

I have a table and i want to write a select command on it and i want to write a command that trace each row and show a field in select result depends on other field of table.
forexample:
my table is

Ali - USA - 3
Tom - UK - 1
John - Canada - 2

I want to write a select command which adds one column depends on the numbers in the third column.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-01 : 06:55:39

can u explain wt result shoould be in ur third column
show some sample output

Exir
Posting Yak Master

151 Posts

Posted - 2009-02-01 : 07:04:14

for example i want to write a select which have a result like this:

NAME - COUNTRY - NUMBER -MAJOR
-------------------------------
Ali - USA - 3 - computer
Tom - UK - 1 - physics
John - Canada - 2 - statistics

the major column is not in database and i want to have it only for showing to the user.
number 3 means computer,2 means physics and 1 means statistics

this was a very simple example similar to what i want
thanks for your help

Exir
Posting Yak Master

151 Posts

Posted - 2009-02-01 : 07:16:03

I think i sould use cursor. what IS your suggestion?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-01 : 07:18:18

declare @tab table (name varchar(32), country varchar(32), number int)
insert into @tab select 'Ali' , 'USA' , 3 union all select
'Tom' , 'UK' , 1 union all select
'John' , 'Canada' , 2

select *,case when number = 3 then 'computer'
when number = 2 then 'statistics'
when number = 1 then 'physics' end as major
from @tab

Exir
Posting Yak Master

151 Posts

Posted - 2009-02-01 : 07:26:12

why you have added all rows in @tab one by one ? i have huge number of rows in my table. how can i add rows one by one?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-01 : 07:28:27

if u are having 1,2,3 in ur number column
then use
select * ,case when number = 3 then 'computer'
when number = 2 then 'statistics'
when number = 1 then 'physics' end as major
from urtablename

Exir
Posting Yak Master

151 Posts

Posted - 2009-02-01 : 07:59:29

yes, thank you :)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-01 : 08:03:16

quote:
Originally posted by Exir

yes, thank you :)

ur welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources