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

Searching Columns for Seperate Strings Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2012 9:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:08 AM
Points: 227, Visits: 719
Hi SQL ServerCentral Team,

I having a table with 5 columns

Record_id Col1 Col2 Col3 Col4 Col5
------------------------------------------------------
101 A A B A A
102 B B NULL NULL B
103 C C C NULL C
104 D C D D D
105 AA A A A A
106 B C A AA D


if record 101 contains all 5 columns same (i.e:A) then only "A" should be inserted into another table
in record 101, col3 contains "B" then it should be skipped.

in record 102 - 3 columns contains B and 2 columns contain NULL, then it should be inserted.

Record 101 - SKIP
Record 102 - Insert B in to another table.
Record 103 - Insert C in to another table.
Record 104 - SKIP

*** if in any record that contains "AA" in any column, it should return "AA" (records 105, 106)

------------
am using below query...
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


it is not working for record_id 105, 106
Post #1373900
Posted Wednesday, October 17, 2012 9:41 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Do it as two queries.

select *
from MyTable
where 'AA' in (Col1, Col2, Col3, Col4, Col5)
union all
select *
from MyTable
where IsNull(Col1, Col2) = IsNull(Col2, Col1)
and IsNull(Col2, Col3) = IsNull(Col3, Col2)
and IsNull(Col3, Col4) = IsNull(Col4, Col3)
and IsNull(Col4, Col5) = IsNull(Col5, Col4);

Something like that. It might suffer performance-wise, but you'll have to test to see if it's good enough for what you need.

If it's too slow, I'd pivot the columns and do your max()=min() compare that way.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1373909
Posted Wednesday, October 17, 2012 9:59 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, September 8, 2014 12:31 PM
Points: 1,480, Visits: 1,029
I was thinking this could work
;WITH MySample AS (
SELECT 101 AS Record_id, 'A' AS Col1, 'A' AS Col2, 'B' AS Col3, 'A' AS Col4, 'A' AS Col5 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' UNION ALL
SELECT 105, 'AA', 'A', 'A', 'A', 'A' UNION ALL
SELECT 106, 'B', 'C', 'A', 'AA', 'D')

SELECT Record_id, Newcol
FROM Mysample
CROSS APPLY (
SELECT Newcol = CASE WHEN MAX(Newcol) = 'AA' THEN 'AA' 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

Which will return 105, but not 106
Wierd that 'AA' is not larger than 'D' The problem here is your using min/max functions on Strings which does not work for this situation.
If you have the ability to influence the design now you should consider changing to Numeric values in these tables.

Consider this simple test, which returns B as the max for Test 2 and not the significantly longer string of A's
From BOL
For character columns, MAX finds the highest value in the collating sequence.

sqlSELECT Test, MIN(Val) AS MinVal, MAX(Val) AS MaxVal
FROM (SELECT 1 AS Test, 'A' AS Val UNION ALL
SELECT 1 AS Test, 'B' AS Val UNION ALL
SELECT 1 AS Test, 'C' AS Val UNION ALL
SELECT 2 AS Test, 'A' AS Val UNION ALL
SELECT 2 AS Test, 'AAAAAAAAAA' AS Val UNION ALL
SELECT 2 AS Test, 'B' AS Val) t
GROUP BY Test

Post #1373921
Posted Thursday, October 18, 2012 6:59 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
The reason "AA" is not "larger" than "D" is because it works simply by ASCII sorting. D comes after AA alphabetically, so D > AA. That's why I suggest two queries.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1374314
Posted Thursday, October 18, 2012 7:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:55 AM
Points: 2,852, Visits: 5,107
Duplicate post with "changed" requirements:

http://www.sqlservercentral.com/Forums/Topic1374194-391-1.aspx


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1374320
Posted Thursday, October 18, 2012 7:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 1,095, Visits: 6,667
Eugene Elutin (10/18/2012)
Duplicate post with "changed" requirements:

http://www.sqlservercentral.com/Forums/Topic1374194-391-1.aspx


I reckon the OP is having some difficulty simplifying the real problem. Here's a solution to the "new" requirements;
;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' UNION ALL
SELECT 105, 'AA', 'A', 'A', 'A', 'A' UNION ALL
SELECT 106, 'B', 'C', 'A', 'AA', 'D')

SELECT Record_id, ISNULL(OldCol, 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
CROSS APPLY (SELECT Oldcol = CASE WHEN 'AA' IN (Col1, Col2, Col3, Col4, Col5) THEN 'AA' ELSE NULL END) y
WHERE Newcol IS NOT NULL
OR Oldcol 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 #1374370
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse