TSQL filtering by character matching
How to filter out the number of counter hits without creating new custom functions (i.e. you can use built-in functions) for the data?
The requirement is to get rows with column numbers gw appearing the same number of times, or if there is another set of sums, their number must be the same. That is, it could be everything like Sandy, or it could be Don, since he has "1" twice and "2" twice. Voland would not qualify as it is "1" twice, but only "2" once, and so on. You don't want to count "0" at all.
login gw1 gw2 gw3 gw4 gw5
Peter 1 0 1 0 0
Sandy 1 1 1 1 0
Voland 1 0 1 2 0
Don 1 2 0 1 2
Diserid output:
login gw1 gw2 gw3 gw4 gw5
Peter 1 0 1 0 0
Sandy 1 1 1 1 0
Don 1 2 0 1 2
The values can be any positive number of times. The values must also be at least double to meet the criteria. So 1 2 3 4 0 is not ok. as each value appears only once. 1 1 0 3 3 is a match.
source to share
WITH Cte(login, gw) AS(
SELECT login, gw1 FROM TestData WHERE gw1 > 0 UNION ALL
SELECT login, gw2 FROM TestData WHERE gw2 > 0 UNION ALL
SELECT login, gw3 FROM TestData WHERE gw3 > 0 UNION ALL
SELECT login, gw4 FROM TestData WHERE gw4 > 0 UNION ALL
SELECT login, gw5 FROM TestData WHERE gw5 > 0
),
CteCountByLoginGw AS(
SELECT
login, gw, COUNT(*) AS cc
FROM Cte
GROUP BY login, gw
),
CteFinal AS(
SELECT login
FROM CteCountByLoginGw c
GROUP BY login
HAVING
MAX(cc) > 1
AND COUNT(DISTINCT gw) = (
SELECT COUNT(*)
FROM CteCountByLoginGw
WHERE
c.login = login
AND cc = MAX(c.cc)
)
)
SELECT t.*
FROM CteFinal c
INNER JOIN TestData t
ON t.login = c.login
First you have a unpivot
table with no inclusions gw
that are 0. Result ( CTE
):
login gw
---------- -----------
Peter 1
Sandy 1
Voland 1
Don 1
Sandy 1
Don 2
Peter 1
Sandy 1
Voland 1
Sandy 1
Voland 2
Don 1
Don 2
Then you execute COUNT(*) GROUP BY login, gw
. The result will be ( CteCountByLoginGw
):
login gw cc
---------- ----------- -----------
Don 1 2
Peter 1 2
Sandy 1 4
Voland 1 2
Don 2 2
Voland 2 1
Finally, we get only those login
with max(cc)
more 1
. This is done to eliminate strings like 1,2,3,4,0
. And login
, whose unique is gw
the same max(cc)
. This is done so that the appearance of the column gw
is the same as the others:
login gw1 gw2 gw3 gw4 gw5
---------- ----------- ----------- ----------- ----------- -----------
Peter 1 0 1 0 0
Sandy 1 1 1 1 0
Don 1 2 0 1 2
source to share
I know I'm late to the party, I can't dial as fast as some and I think I arrived 40 minutes late, but since I did I thought I'd share it. My method used univot and pivot to achieve the result:
Select *
from foobar f1
where exists
(Select * from
(Select login_, Case when [1] = 0 then null else [1] % 2 end Val1, Case when [2] = 0 then null else [2] % 2 end Val2,
Case when [3] = 0 then null else [3] % 2 end Val3, Case when [4] = 0 then null else [4] % 2 end Val4, Case when [5] = 0 then null else [5] % 2 end Val5
from
(Select *
from
(select * from foobar) src
UNPIVOT
(value for amount in (gw1, gw2, gw3, gw4, gw5)) unpvt) src2
PIVOT
(count(amount) for value in ([1],[2],[3],[4],[5])) as pvt) res
Where 0 in (Val1,Val2, Val3, Val4, Val5) and not exists (select * from foobar where 1 in (Val1, Val2, Val3, Val4, Val5)) and login_ = f1.login_)
and here is the fiddle: http://www.sqlfiddle.com/#!6/b78f8/1/0
source to share
I think this logic is correct, namely
Find lines where there is at least one even number that is equal gws
and not an instance of an odd number gws
. Zeros excluded
Find lines where there is at least one number equal
gws
and no instance of onegws
. Zero values are excluded.
Unpivot is used to simplify column-based reasoning gwx
and CTEs are used to prevent repetition.
WITH unpvt AS
(
SELECT *
FROM MyTable
UNPIVOT
(
gwvalue
for z in (gw1, gw2, gw3, gw4, gw5)
) x
),
grp AS
(
SELECT [login], gwvalue, COUNT(gwvalue) gwCount
FROM unpvt
WHERE gwvalue > 0
GROUP BY [login], gwvalue
)
SELECT
*
FROM MyTable mt
WHERE EXISTS
(
SELECT 1
FROM grp g
WHERE g.[login] = mt.[login]
AND gwCount > 1
)
AND NOT EXISTS
(
SELECT 1
FROM grp g
WHERE g.[login] = mt.[login]
AND gwCount = 1
);
source to share