question

sqlLearner 1 avatar image
sqlLearner 1 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
tsqlcase-statement
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
TimothyAWiseman avatar image
TimothyAWiseman answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.