asked
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
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
8 People are following this question.
