Search Number repeated in SQL SERVER

Navigation
-1

How can I find a repeated number in a Table Items in different Columns called Gem1, Gem2, Gem3, Gem4, Gem5, Gem6 but that are repeated in the same row. example: 

 UsuarioID, Gem1, Gem2, Gem3, Gem4, Gem5, Gem6
 0001       23    23    23    23    23    23
 0002       31    23    34    2     34    5
 0003       32    3     35    23    23    23
 0004       35    3     36    230   231   255
 0005       0     0     0     0     0     0

I need to make a select and an Update that replaces all the numbers that are repeated in a same row and I put number 0 thank you in advance.

Imagining that it is a game where there are cheaters who like to have a repeated number to exit "winners" example 0001 Repeat number 23 in all its six rows , while 0002 repeats twice the number 34 and 0003 repeats the number 23 three times but you see 0004 who is NOT TRAMPOSO has their different numbers and passes the 0005 test whose number by defauld in the data Base is Zero should be omitted because it means that it does not have a trap, making it clear that the numbers that can be repeated go from 1 to 255

My question is how can I eliminate cheats whose table is designed exactly like this?

    
asked by Juan Carlos Villamizar Alvarez 18.03.2017 в 02:51
source

1 answer

1

One possible solution is to generate a transposed version of your data and do the duplicate count based on that transformation. That is, convert the table to a vertical version to facilitate operations; for example: 

id   gem    num
---- ------ -----
1    Gem1   23
1    Gem2   23
1    Gem3   23
1    Gem4   23
1    Gem5   23
1    Gem6   23
2    Gem1   31
. . .

The only requirement (and do not indicate in the example of the data in your question) is the existence of a key field that identifies each row (eg a field type IDENTITY ).

Next we can obtain the count ( COUNT ) of the data in that transposed table, grouping by each row (that is, by the key or id ) and number, getting something like this: 

id   num   count
---- ----- -------
1    23    6
2    34    2
3    23    3

and finally use that count to update the data in your original table to the desired value (zero).

To illustrate the solution I prepared this SQL code so you can see it in action; the exercise uses temporary tables to generate the intermediate data; however, the code could be simplified.

Note: The attached solution will even find several sets of duplicate numbers, that is, for the following data line: 

Gem1  Gem2  Gem3  Gem4  Gem5  Gem6
23    8     23    17    14    14

will identify both number 23 and number 14 as duplicates and will eventually update them to zero as well.

    
answered by 18.03.2017 в 07:28
Syntax error, unexpected; expecting) how can I use the variables I receive by post in all my html pages