How to check and remove duplicates in several columns, replacing them with NULL Example:

1 2050 2030 2050 2 2040 2030 2050 3 2020 2020 2000 4 2015 2015 2000

It should be:

 1 2050 2030 NULL 2 2040 NULL NULL 3 2020 NULL 2000 4 2015 NULL NULL

Speakers can be unlimited number. Thank you in advance.

  • Describe the logic of duplicate search in more detail, no matter how much I looked at the presented data, I could not understand what patterns you see. If the line should not contain duplicates of numbers, then the first line is clear (2050 met twice, the second is zeroed), but then the second flow is not clear, there all the numbers are unique. If you look for duplicates in separate columns, then the first column is clear, the second is not. I didn’t search for diagonals, it seems that wasn’t what you meant. - AK ♦
  • In the current formulation of the question - no way. First, it is generally not clear what you called "duplicates." Secondly, even if there is a certain double, there is no criterion by which it is unambiguously clear which one to leave. and what about NULLity - Akina
  • Suppose there are ID, Number1, Number2, Number3 ID fields unique, and the number can be filled in as in a tempo table or in CTE. It is important to check both in the row 1, 2, 3 and further for uniqueness, and by the column Number1, Number2, Number3. If a duplicate is found, it is replaced with NULL - Uncle Drew
  • Oh my god, I found the system. It is necessary to unwind the entire table into a one-dimensional array, then for each number replace all occurrences except the first one to zero, then project the data back into places. Those. 2050, 2030, 2050 (zero), continue from the beginning of the second runoff 2040, 2030 (was earlier, zero), 2050 (was earlier, zero), go to the third line .... Not sure I understood the algorithm correctly, but the The example is completely covered by this algorithm. The practicality of the problem is questionable, but you can make a small kodgolf. - AK ♦
  • one
    Unfortunately, it is difficult to understand the implementation. Until the DBMS is announced, it is simply impossible. I really don’t hope to learn the version ... but in general the problem is simple. The easiest way is probably to solve in a stored procedure format using a temporary table. - Akina

0