October 17, 2012 at 2:36 am
;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')
October 17, 2012 at 3:07 am
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
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 18, 2012 at 5:08 am
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
;
October 19, 2012 at 7:26 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply