select using case

hello experts:

I have a situatioln in sql where i want to select from but if the column value is let's say apple i want to select as fruit.

from db  table = table1                        
column name =  fruits
values
apple
bananas    

column name =  veggies
values
pepper
onion  

when i select i dont' wnagt to save as apple or bananas but instead the query  should retrun fruit

select  fruits as fruit , veggies as veg
   from table1
   where fruits = 'apple' or fruits = 'bananas'    

I hope I make sense. maybe I need a case statement.

Thanks
         
lancerxeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Christopher GordonSenior Developer AnalystCommented:
Here are some ideas.  Just copy and past into management studio.
declare @mytable table (id int identity(1,1), code nvarchar(30))

insert into @mytable values('apple')
insert into @mytable values('cherry')
insert into @mytable values('pepper')
insert into @mytable values('onion')

select	
	*,
	
	case 
		when code = 'apple' then 'fruit'
		when code = 'cherry' then 'fruit'
		when code = 'pepper' then 'vegetable'
		when code = 'onion' then 'vegetable'
	end as FoodType
	
	,case 
		when code = 'apple' then 1
		when code = 'cherry' then 1
		when code = 'pepper' then 0
		when code = 'onion' then 0
	end as IsFruit
	

	,case 
		when code = 'apple' then 0
		when code = 'cherry' then 0
		when code = 'pepper' then 1
		when code = 'onion' then 1
	end as IsVegatable

from	@mytable

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LowfatspreadCommented:
yes you need to use a case statement probably
but you should also have a "category" table to make the definition of fruit or vegetable generic...

e.g.
select case when c.category = 'fruit' then t.code end as fruit,case when c.category='vegetable' then t.code end as Veggie
from  yourtable as t
left outer join categories as c
 on t.code=c.code
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.