April 21, 2003 at 11:21 am
I have a table which holds dubplicates of household ids, call this table household, for example,
household_id
1
1
2
3
3
4
4
4
5
I wanted to uniquely identify this data into each row have a unique value in the format below where the duplicate has a number assign to it on the right
re_household_id
1-1
1-2
2-1
3-1
3-2
4-1
4-2
4-3
5-1
how can this be done in SQL?
April 21, 2003 at 12:31 pm
There is no 'elegant' way to do this, but there are a couple of different ways that this can be done.
The most certain one that I can code uses a cursor:
Create Procedure usp_UniquelyIdentify
AS
declare c_hID CURSOR For
Select Household_ID FROM Household
Declare @hID int
Declare @lastID int
Declare @uhID int
Set @LastID = -1
Open c_HID
Fetch Next from c_HID INTO @hID
While @@FETCH_STATUS = 0
BEGIN
If @LastID <> @hID
BEGIN
Set @uhID = 1
Set @LastID = @hID
END
ELSE
Set @uhID = @uhID + 1
Update Household Set re_household_id = Cast(@hID as varchar) + '-' + Cast(@uhID As Varchar)
WHERE CURRENT OF c_HID
Fetch Next from c_HID INTO @hID
END
Close c_HID
Deallocate c_HID
Run the SP, and the re_household_id field should be populated as you need it to be.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply