Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Append consecutive integers to duplicate values?

Author  Topic 

dwooderson
Starting Member

7 Posts

Posted - 2012-05-23 : 16:20:20
I'm trying to integrate non-unique data into a system that requires uniqueness, but have a special requirement for how to make the values unique. They want to append integers to all duplicates starting at 1.

I'm completely stuck. Any ideas?

Example:

create table #Names (name varchar(20))
go

insert into #names values ('Alice')
insert into #names values ('Carol')
insert into #names values ('June')
insert into #names values ('Carol')
insert into #names values ('June')
insert into #names values ('Mary')
insert into #names values ('Karen')
insert into #names values ('June')
insert into #names values ('Mary')
insert into #names values ('Jennifer')

select * from #names order by name

Result:

Alice
Carol
Carol
Jennifer
June
June
June
Karen
Mary
Mary

Desired:

Alice
Carol 1
Carol 2
Jennifer
June 1
June 2
June 3
Karen
Mary 1
Mary 2

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-23 : 17:30:52
[code]



create table #Names (name varchar(20))
go

insert into #names values ('Alice')
insert into #names values ('Carol')
insert into #names values ('June')
insert into #names values ('Carol')
insert into #names values ('June')
insert into #names values ('Mary')
insert into #names values ('Karen')
insert into #names values ('June')
insert into #names values ('Mary')
insert into #names values ('Jennifer')

select * from #names order by name

;WITH CTE AS
(
SELECT ROW_NUMBER()OVER ( PARTITION BY NAME ORDER BY (SELECT NULL)) RN,
COUNT(*) OVER ( PARTITION BY NAME) [COUNT] ,* FROM

#names)



UPDATE CTE SET name = name + CHAR(10)+ CAST(RN AS CHAR(3))
WHERE [COUNT]>1
---select * from #names order by name


[/code]

Vijay is here to learn something from you guys.
Go to Top of Page

dwooderson
Starting Member

7 Posts

Posted - 2012-05-24 : 08:32:22
Nice! I knew there was an efficient way to accomplish this, I was considering everything from stored procedures with cursors, to appending to everything, regardless of if it is a dup.

Thanks!!
Go to Top of Page
   

- Advertisement -