CODEWITHSUNDEEP
sqlsql-server-2012ssms
Tassisto
Tassisto

Reputation: 10345

How to make duplicate values unique?

I have records with duplicate values in a column.

For example:

INVOICENUM  PRODUCT
-------------------
  789789    ABROPT
  789789    ABROPT
  719648    AZGTEL
  754114    GRTYPT

I have 2 records with duplicate invoice numbers, for the first number I want to add 'a' for the second I want to add 'b' and so on.

INVOICENUM  PRODUCT
-------------------
 789789a    ABROPT
 789789b    ABROPT
 719648     AZGTEL
 754114     GRTYPT

How can I fix this with SQL - I'm using SQL Server 2012 and Microsoft SQL Server Management Studio 11.0.3000.0?

Upvotes: 2

Views: 234

Answers (2)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186668

Often such problems are solved via Analytic functions.

select case
         when (count(1) over(partition by InvoiceNum) > 1) then
           InvoiceNum + Char(row_Number() over(partition by InvoiceNum order by InvoiceNum) + 96)
         else
           InvoiceNum
       end as InvoiceNum,
       Product    
  from MyTable

The main advantage of Analytic functions is the performance.

Upvotes: 2

Chris Moutray
Chris Moutray

Reputation: 18349

Ok well assuming SQL Server (with rownumber feature) then this seems to work

select
    INVOICENUM = case 
        when INVOICENUM in (select INVOICENUM from YourTable group by INVOICENUM having COUNT(*) > 1)
        then INVOICENUM + char(96+row_number() over (partition by INVOICENUM order by INVOICENUM))
        else INVOICENUM 
    end,
    PRODUCT,
    OtherColumn,
    AnotherColumn
from 
    YourTable

Although its not particularly elegant...

The idea here is to get a row number to seed the int-to-character function CHAR(...). Where 96 is the ASCII code for the character before a - so rownumber of 1 gives a (ie Ascii code of 97), row number of 2 gives b (ie Ascii code of 98) and so on... I've thrown the case statement in there to exclude scenarios when there are no duplicates and so it just gives back the InvoiceNum without adding a letter...

Heres my test example...

declare @data table (INVOICENUM nvarchar(100), PRODUCT nvarchar(100))

insert into @data
values ('789789', 'ABROPT')
,('789789', 'ABROPT')
,('719648', 'AZGTEL')
,('754114', 'GRTYPT')

select
    INVOICENUM = case 
        when INVOICENUM in (select INVOICENUM from @data group by INVOICENUM having COUNT(*) > 1)
        then INVOICENUM + char(96+row_number() over (partition by INVOICENUM order by INVOICENUM))
        else INVOICENUM 
    end,
    PRODUCT
from 
    @data

Upvotes: 1

Related Questions