SQLTeam.com | Weblogs | Forums

Adding _1,2 3 etc to duplicates data

Hi

I have the following sql
[code]
merge into [dbo].[BNYUKWorkingDataloadFile1] tb
using (
SELECT pk_id,[MasterIdentifier],[FundCode],
CAST([MasterIdentifier]as varchar(10))+'_'+
CAST(ROW_NUMBER() OVER (PARTITION BY[MasterIdentifier],[Fundcode]
ORDER BY [pk_id]asc) as varchar(10))
AS RowNumber
FROM [dbo].[BNYUKWorkingDataloadFile1]
where [MasterIdentifier] in (
SELECT [MasterIdentifier] FROM [dbo].[BNYUKWorkingDataloadFile1]
GROUP BY [MasterIdentifier] HAVING ( COUNT([MasterIdentifier]) > 1 ))
) t on (t.pk_id = tb.pk_id)
when matched then update
set [MasterIdentifier] = t.RowNumber;

[\code]

It gets dupicate master id for the same fund code.

what i want is the following output

FundCode MasterIdentifier
262050 CABVSS671
297950 CABVSS671
297950 CABVSS671_1
297950 CABVSS671_2

but what im getting is

FundCode MasterIdentifier
262050 CABVSS671_1
297950 CABVSS671_1
297950 CABVSS671_2
297950 CABVSS671_3

can anyone help with this

I think this will work, but not usable sample data so I can't test it:

merge into [dbo].[BNYUKWorkingDataloadFile1] tb
using (
SELECT pk_id,[MasterIdentifier],[FundCode],
    ROW_NUMBER() OVER (PARTITION BY[MasterIdentifier],[Fundcode]
        ORDER BY [pk_id]asc) - 1 AS RowNumber
FROM [dbo].[BNYUKWorkingDataloadFile1]
where [MasterIdentifier] in (
SELECT [MasterIdentifier] FROM [dbo].[BNYUKWorkingDataloadFile1]
GROUP BY [MasterIdentifier] HAVING ( COUNT([MasterIdentifier]) > 1 ))
) t on (t.pk_id = tb.pk_id)
when matched then update
set [MasterIdentifier] = t.[MasterIdentifier] +
    case when RowNumber = 0 then '' else '_' + CAST(RowNumber AS varchar(10)) end;
1 Like

Thanks that worked as i wanted it to

hi

i tried to do this with sample data ... if it helps great :slight_smile: :slight_smile:

drop create sample data ...
drop table #data 
go 

create table #data 
(
FundCode int, 
MasterIdentifier varchar(100)
)
go 

insert into #data select 262050,'CABVSS671'
insert into #data select 297950,'CABVSS671'
insert into #data select 297950,'CABVSS671'
insert into #data select 297950,'CABVSS671'
go
SQL .....
;WITH cte 
     AS (SELECT Row_number() 
                  OVER( 
                    partition BY fundcode, masteridentifier 
                    ORDER BY fundcode, masteridentifier) - 1 AS rn, 
                * 
         FROM   #data) 
SELECT fundcode, 
       CASE 
         WHEN rn <> 0 THEN masteridentifier + '_' + Cast(rn AS VARCHAR) 
         ELSE masteridentifier 
       END 
FROM   cte 

go