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.
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 nameResult:AliceCarolCarolJenniferJuneJuneJuneKarenMaryMaryDesired:AliceCarol 1Carol 2JenniferJune 1June 2June 3KarenMary 1Mary 2 |
|
vijays3
Constraint Violating Yak Guru
354 Posts |
Posted - 2012-05-23 : 17:30:52
|
[code]create table #Names (name varchar(20))goinsert 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. |
 |
|
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!! |
 |
|
|
|
|