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

avatar image

sqlLearner 1
972 38 50 57

(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

avatar image

TimothyAWiseman
15.6k 22 49 38

(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.

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:

x389
x12

asked: Jul 11, 2012 at 11:27 PM

Seen: 8444 times

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

Copyright 2016 Redgate Software. Privacy Policy