DECLARE @Sample TABLE
(
ID VARCHAR(10),
[Type] VARCHAR(10),
Size INT
)
INSERT @Sample
VALUES ('Valve1', 'Valve', 100),
('Valve2', 'Valve', 150),
('Manhole', 'Manhole', 1050),
('Manhole', 'Manhole', 1050),
('Cesspit1', 'Cesspit', 200),
('Cesspit2', 'Cesspit', 200)
SELECT * FROM @Sample
-- SwePeso
;WITH cteTarget(ID, SeqID, Items)
AS (
SELECT ID,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Size) AS SeqID,
COUNT(*) OVER (PARTITION BY ID) AS Items
FROM @Sample
)
UPDATE cteTarget
SET ID = ID + CAST(SeqID AS VARCHAR(12))
WHERE Items > 1
SELECT * FROM @Sample
N 56°04'39.26"
E 12°55'05.63"
Update column with duplicates to unique values
Posted - 05/15/2012 : 00:01:30
