Compare multiple columns, but only those having valid values, and create y/n flag if all are equal

sql, sql-server, sql-server-2008

Solution

SELECT ID, 
       CASE 
         WHEN C = 1 THEN 'Y' 
         ELSE 'N' 
       END AS DFLAG 
FROM   z_test 
       CROSS APPLY (SELECT COUNT(DISTINCT D) C 
                    FROM   (VALUES(D1), 
                                  (D2), 
                                  (D3), 
                                  (D4)) V(D) 
                    WHERE  LEN(D) > 0 /*Excludes blanks and NULLs*/
                         AND D LIKE '%[^0]%'/*Excludes ones with only zero*/) CA

Problem

I want to create a Y/N flag, where Y indicates every valid value in every column in a given row is equal, and N otherwise. I need to exclude from consideration any column that contains nulls, blanks, or all zeroes. Suppose: ``` CREATE TABLE z_test (ID INT NOT NULL, D1 VARCHAR(8)NULL, D2 VARCHAR(8)NULL, D3 VARCHAR(8)NULL, D4 VARCHAR(8)NULL, DFLAG CHAR(1)NULL) INSERT INTO z_test VALUES (1,NULL,' ','000000','00000000',NULL) INSERT INTO z_test VALUES (1,'20120101','0000','20120101','00000000',NULL) INSERT INTO z_test VALUES (2,'20100101','20100101','20100101','20100101',NULL) INSERT INTO z_test VALUES (2,'00000000','20090101','0','20090101',NULL) INSERT INTO z_test VALUES (3,'00000000','20090101',NULL,'20120101',NULL) INSERT INTO z_test VALUES (3,'20100101',' ',NULL,'20100101',NULL) ``` The desired output (excluding D1 through D4, though I don't want to drop them) is: ``` ID DFLAG --------------- 1 N 1 Y 2 Y 2 Y 3 N 3 Y ``` Speed is not a concern as this query will not be run very often but it is on a largish table. Any pointers or suggestions would be very much appreciated!!

Original source