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?
 
Christopher GordonConnect With a Mentor Senior 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
 
LowfatspreadConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.