x

CASE STATEMENT USING MULTIPLE COLUMNS

I am trying to wirte a CASE statement based on values from multiple columns. I am having trouble getting it to work properly. Does anyone have any ideas. The CASE Statement below should explain what I am trying to accomplish.

 SELECT colA,colB

 [TEST] = CASE 
 WHEN  (ColA <> '' AND ColB <> '')
 THEN 'BOTH NOT BLANK'

 WHEN (COLA = '' AND ColB <> '')
 THEN 'ColB NOT BLANK But ColA is Blank'

 WHEN (COLA <> '' AND ColB = '')
 THEN 'ColB is BLANK But ColA is NOT Blank'

 WHEN (ColA = '' AND ColB= '')
 THEN 'Both Columns Blank'
 END

       FROM TEST_tbl
more ▼

asked Jul 11, 2012 at 11:27 PM in Default

sqlLearner 1 gravatar image

sqlLearner 1
792 36 39 46

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Your example looks like it would fail for having no comma after colB.

Here is a sample of using a case statement based on multiple columns:

create table #temp (cola varchar(10), colb varchar(10), colc varchar (10))

insert into #temp
values 
 ('1', '2', 'both'),
 ('', '', 'neither'),
 ('col1', 'col2', 'both'),
 ('', 'col2', 'justb'),
 ('col1', '', 'justa')

select 
 case
 when cola = '' and colb = '' then 'neither'
 when (cola <> '' and colb <> '') then 'both'
 when (cola = '' and colb <> '') or (colb = '' and cola <> '') then 'Just one'
 end as HowMany,
 colc
from #temp


drop table #temp

and the results are:

HowMany colc
both both
neither neither
both both
Just one justb
Just one justa
more ▼

answered Jul 12, 2012 at 12:00 AM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x288
x40

asked: Jul 11, 2012 at 11:27 PM

Seen: 2500 times

Last Updated: Jul 12, 2012 at 08:59 AM