Making duplicates unique

Question

Making duplicates unique

bcronce

SSCrazy

Points: 2597
More actions
June 26, 2008 at 7:45 am

#193737

I'm doing this right now to convert duplicate rows
Create Table #Dups
(
vchSectionNumber VARCHAR(50),
vchCourseID VARCHAR(50),
vchSchoolPeriodID VARCHAR(50),
vchsisclassid VARCHAR(50)
)
--This finds duplicate Section Numbers, then appends an incremental number to make them unique
INSERT #Dups (vchSectionNumber, vchCourseID, vchSchoolPeriodID, vchSISClassID)
SELECT c1.vchSectionNumber + ': ' + convert(varchar, ROW_NUMBER() OVER(ORDER BY c1.vchSectionNumber)), c1.vchCourseID, c1.vchSchoolPeriodID, c1.vchSISClassID
from Table c1
INNER JOIN
(SELECT vchSectionNumber, vchCourseID, vchSchoolPeriodID
FROM Table c
WHERE c.iConversionRun = @iConversionRun
GROUP BY vchSectionNumber, vchCourseID, vchSchoolPeriodID
HAVING count(vchsisclassid) > 1) c2 on c1.vchSectionNumber = c2.vchSectionNumber
and c1.vchCourseID = c2.vchCourseID
and c1.vchSchoolPeriodID = c2.vchSchoolPeriodID
UPDATE Table
SET Table.vchSectionNumber = c1.vchSectionNumber
FROM #Dups c1
WHERE Table.vchCourseID = c1.vchCourseID AND Table.vchSchoolPeriodID = c1.vchSchoolPeriodID AND Table.vchsisclassid = c1.vchsisclassid
DROP TABLE #Dups
That is converting
LastName, FirstName
LastName, FirstName
LastName, FirstName
into
LastName, FirstName: 7106
LastName, FirstName: 7107
LastName, FirstName: 7108
Is there a way to make it reset the increment per "LastName, FirstName" combination?
So I would get
LastName, FirstName: 1
LastName, FirstName: 2
LastName, FirstName: 3
DiffLastName, DiffFirstName: 1
Also, is there a way of doing this in SQL 2000 if I would need to?
Thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

In 2005, take a look at Row_Number in Books Online. It's very simple and easy to use.

For 2000, take a look at Jeff Moden's Running Totals article on this site. You should be able to use a variation on that. Bit more complex than Row_Number, but it can do what you want.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

GSquared (6/26/2008)
For 2000, take a look at Jeff Moden's Running Totals article on this site. You should be able to use a variation on that. Bit more complex than Row_Number, but it can do what you want.

Thanks for the plug, Gus...

The article is at the following URL...

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)