question

David 2 1 avatar image
David 2 1 asked

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
sql-server-2012selectpivotcase-statement
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
mjharper avatar image
mjharper answered
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'
1 comment
10 |1200 characters needed characters left characters exceeded

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

@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.
0 Likes 0 ·

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.