question

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