How to check if there is a number in several fields of a table

Asked

Viewed 122 times

2

I have a table in a Sqlserver database that simulates a bingo card. In total there are 25 fields that represent the card: n1, n2, n3, ..., n25

Let’s say I drew the numbers 5, 28 and 67, as I do a select to see if the current chart has these numbers in the fields I mentioned above?

I need something like this select * from cartelas where n1, n2, n3..., n25 in [5, 28, 67]

I know it’s too much to ask, but the result would have to be similar to:

Cartela | Acertos 1 | 3 2 | 3 3 | 2 4 | 1 5 | 1 6 | 1

I don’t even know if something like this is possible just by using select, but I’m waiting for some suggestion.

sql-server query select

  • It seems to me that the simplest form is through the use of bit map.

1 answer

2


I managed to solve using the code below:

select b.*, s.cnt
from cartela b cross apply
    (select count(*) as cnt
        from (values (n1), (n2), (n3), (n4), (n5), (n6), (n7), (n8), (n9), (n10), (n11), (n12), (n13), (n14), (n15), (n16), (n17), (n18), (n19), (n20), (n21), (n22), (n23), (n24), (n25)) v(n)
        where n in (5, 28, 67)
        ) s
order by s.cnt desc

Browser other questions tagged sql-server query select

You are not signed in. Login or sign up in order to post.