Here is an example based on Gerry recommendation. I'm guessing it based on what posted here. First, find out which row has duplicate then capture the Id. After that, query the original table which contain the duplicate id with Row_Number. Hope that help.
DECLARE @temp1 TABLE (
Civilidd INT ,
[Name] VARCHAR(50) ,
[Address] VARCHAR(50)
)
INSERT INTO @temp1
SELECT 1, 'a1', 'address 1' UNION
SELECT 1, 'a1 x 2', 'address 1 x 2' UNION
SELECT 1, 'a1 x 3', 'address 1 x 3' UNION
SELECT 2, 'a2', 'address 1' UNION
SELECT 3, 'a2', 'address 1' UNION
SELECT 3, 'a2 x 2', 'address 3 x 2' UNION
SELECT 4, 'a4', 'address 4'
;WITH cteCount AS (
SELECT Civilidd FROM @temp1
GROUP BY Civilidd
HAVING COUNT(Civilidd) > 1
) SELECT t.* , ROW_NUMBER() over (partition by t.CIVILIDD order by t.CIVILIDD ) as RowNumber
FROM @temp1 t JOIN cteCount cc ON t.Civilidd = cc.Civilidd
Output:
Civilidd Name Address RowNumber
1 a1 address 1 1
1 a1 x 2 address 1 x 2 2
1 a1 x 3 address 1 x 3 3
3 a2 address 1 1
3 a2 x 2 address 3 x 2 2