Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Checking values in multiples columns Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2012 2:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:09 AM
Points: 227, Visits: 715
;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')
Post #1373677
Posted Wednesday, October 17, 2012 3:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:34 PM
Points: 1,090, Visits: 6,538
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 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




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
Post #1373694
Posted Thursday, October 18, 2012 5:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, January 17, 2014 6:38 AM
Points: 278, 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 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
;

Post #1374274
Posted Friday, October 19, 2012 7:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 6:23 AM
Points: 1,945, Visits: 2,922
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?

CREATE TABLE Mysamples
(sample_id INTEGER NOT NULL PRIMARY KEY,
col1 CHAR(1) CHECK (col1 IN ('A', 'B', 'C', 'D')),
col2 CHAR(1) CHECK (col2 IN ('A', 'B', 'C', 'D')),
col3 CHAR(1) CHECK (col3 IN ('A', 'B', 'C', 'D')),
col4 CHAR(1) CHECK (col4 IN ('A', 'B', 'C', 'D')),
col5 CHAR(1) CHECK (col5 IN ('A', 'B', 'C', 'D'))
);

INSERT INTO Mysamples
VALUES(101, 'A', 'A', 'B', 'A', 'A' ),
(102, 'B', 'B', NULL, NULL, 'B'),
(103, 'C', 'C', 'C', NULL, 'C' ),
(104, 'D', 'C', 'D', 'D', 'D');


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1374811
Posted Friday, October 19, 2012 7:26 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Friday, August 22, 2014 9:02 AM
Points: 689, Visits: 2,779
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
Post #1374816
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse