x

CASE Statement Not Returning All Values?

Hi there,

I'm in a bit of a pickle trying to display all results from a CASE and UNPIVOT statement. Here is the test data:

 CREATE TABLE t1(
 ID INT,
 DOOR1 VARCHAR(5),
 DOOR2 VARCHAR(5),
 DOOR3 VARCHAR(5),
 STEP1 VARCHAR(5),
 STEP2 VARCHAR(5),
 STEP3 VARCHAR(5))
 
 INSERT INTO t1
 SELECT 100,'True','True','True','True','False','False'
 UNION 
 SELECT 200,'True','False','True','True','False','False'
 UNION 
 SELECT 300,'False','True','True','False','False','False'
 UNION 
 SELECT 400,'True',NULL,'True','True','False','False'
 UNION 
 SELECT 500,NULL,'True','False','False',NULL,'False'

And here is my CASE and UNPIVOT statement:

 SELECT ID       
         ,DynamicValue
 FROM (SELECT ID 
             ,CAST(CASE WHEN [DOOR1] = 'True' OR [DOOR2] = 'True' OR [DOOR3] = 'True' THEN 'Door' ELSE     'False' END AS VARCHAR(100)) AS [DOOR]
             ,CAST(CASE WHEN [STEP1] = 'True' OR [STEP2] = 'True' OR [STEP3] = 'True' THEN 'Step' ELSE     'False' END AS VARCHAR(100)) AS [STEP]
             FROM t1) p
 UNPIVOT (DynamicValue FOR Condition IN ([DOOR]
                                         ,[STEP])) AS u
                                         WHERE DynamicValue <> 'False'
                                         ORDER BY 1

Which displays the results:

 ID    DynamicValue
 100    Door
 100    Step
 200    Door
 200    Step
 300    Door
 400    Door
 400    Step
 500    Door

Here you will notice that the results do not display a row for each ID with all types of DOOR or STEP. In essence I am looking to return a row for each 'True' value, such as:

 ID DynamicValue
 100    Door
 100    Door
 100    Door
 100    Step
 200    Door
 200    Door
 200    Step
 300    Door
 300    Door
 400    Door
 400    Door
 400    Step
 500    Door

There are 13 rows returned. One for each 'True' value in the test data. What is my query doing incorrectly?

TIA

more ▼

asked Oct 20 at 10:44 AM in Default

avatar image

David 2 1
1.3k 56 61 69

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

1 answer: sort voted first

Hi,

I would do the unpivot first and then apply a case statement to the results of that. Something like this:

 SELECT ID,
        CASE WHEN Condition LIKE 'DOOR%' THEN 'DOOR' ELSE 'STEP' END AS Value
 FROM
 (SELECT * FROM #t1) t UNPIVOT(DynamicValue FOR Condition IN(DOOR1, DOOR2, DOOR3, STEP1, STEP2, STEP3)) AS unpvt
 WHERE DynamicValue <> 'False'
more ▼

answered Oct 20 at 10:57 AM

avatar image

mjharper
2.2k 3 8 14

@mjharper thank you very much, that was very much puzzling me. With a slight tweak I've got the live results I was after. Have a great weekend, and thanks again.

Oct 20 at 11:31 AM David 2 1
(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:

x397
x159
x114
x15

asked: Oct 20 at 10:44 AM

Seen: 21 times

Last Updated: Oct 20 at 11:31 AM

Copyright 2017 Redgate Software. Privacy Policy