## Checking values in multiples columns

 Author Message Minnu Hall of Fame Group: General Forum Members Points: 3259 Visits: 950 ;WITH Mysample (Record_id, Col1, Col2, Col3, Col4, Col5) AS (SELECT 101, 'A', 'A', 'B', 'A', 'A' UNION ALL SELECT 102, 'B', 'B', NULL, NULL, 'B' UNION ALL SELECT 103, 'C', 'C', 'C', NULL, 'C' UNION ALL SELECT 104, 'D', 'C', 'D', 'D', 'D') ChrisM@home SSChampion Group: General Forum Members Points: 12879 Visits: 10881 CROSS APPLY VALUES was made for this;`;WITH Mysample (Record_id, Col1, Col2, Col3, Col4, Col5) AS ( SELECT 101, 'A', 'A', 'B', 'A', 'A' UNION ALL SELECT 102, 'B', 'B', NULL, NULL, 'B' UNION ALL SELECT 103, 'C', 'C', 'C', NULL, 'C' UNION ALL SELECT 104, 'D', 'C', 'D', 'D', 'D')SELECT Record_id, Newcol FROM MysampleCROSS APPLY ( SELECT Newcol = CASE WHEN MAX(Newcol) = MIN(Newcol) THEN MAX(Newcol) ELSE NULL END FROM (VALUES (Col1), (Col2), (Col3), (Col4), (Col5)) X (Newcol) ) dWHERE Newcol IS NOT NULL` Low-hanging fruit picker and defender of the moggies For better assistance in answering your questions, please read this. Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden paul_ramster SSC Eights! Group: General Forum Members Points: 906 Visits: 534 Presumably the OP's question was to find rows where all columns have the same value???CROSS APPLY VALUES is definitely the way to go, but your CASE statement made me scratch my head for a while to determine the logic being implemented. To my mind, the following expresses that logic for more clearly.ymmv, obviously`WITH Mysample (Record_id, Col1, Col2, Col3, Col4, Col5) AS ( SELECT 101, 'A', 'A', 'B', 'A', 'A' UNION ALL SELECT 102, 'B', 'B', NULL, NULL, 'B' UNION ALL SELECT 103, 'C', 'C', 'C', NULL, 'C' UNION ALL SELECT 104, 'D', 'C', 'D', 'D', 'D')SELECT m.Record_id, d.MAXVAL as NewColFROM Mysample mCROSS APPLY ( SELECT MAX(X.Newcol) as MAXVAL, MIN(X.Newcol) as MINVAL FROM (VALUES (m.Col1), (m.Col2), (m.Col3), (m.Col4), (m.Col5)) X (Newcol) ) dWHERE d.MAXVAL = d.MINVAL;` Andy Hyslop SSCertifiable Group: General Forum Members Points: 5095 Visits: 3060 Where is the DDL? What is yuor question? Why did post half a statement in 1970's Sybase dialect? Is this what you meant, but did not know how to write? I wasn't aware that Sybase 1970 had Common Table Expressions, I thought that came in SQL 2005 ;-) ==========================================================================================================================A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe