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
Best Answer
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
