Checking values in multiples columns

Question

Post reply

Checking values in multiples columns

Minnu

SSCertifiable

Points: 6451
More actions
October 17, 2012 at 2:36 am

#283515

;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')

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

CROSS APPLY VALUES[/url] 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 Mysample

CROSS APPLY (

SELECT Newcol = CASE WHEN MAX(Newcol) = MIN(Newcol) THEN MAX(Newcol) ELSE NULL END

FROM (VALUES (Col1), (Col2), (Col3), (Col4), (Col5)) X (Newcol)

) d

WHERE Newcol IS NOT NULL

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

paul_ramster Ten Centuries Points: 1382 More actions · Answer 2

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 NewCol

FROM Mysample m

CROSS 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)

) d

WHERE d.MAXVAL = d.MINVAL

;

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 3

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