October 17, 2012 at 9:27 am
Hi SQL ServerCentral Team,
I having a table with 5 columns
Record_idCol1Col2Col3Col4Col5
------------------------------------------------------
101AABAA
102BBNULLNULLB
103CCCNULLC
104DCDDD
105AAAAAA
106BCAAAD
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
October 17, 2012 at 9:41 am
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
October 17, 2012 at 9:59 am
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
October 18, 2012 at 6:59 am
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
October 18, 2012 at 7:03 am
Duplicate post with "changed" requirements:
http://www.sqlservercentral.com/Forums/Topic1374194-391-1.aspx
October 18, 2012 at 7:49 am
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
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]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply