Adding a count variable as a suffix in sql

Question

I want to create a new view in SQL Server 2008 R2. Given is a col called "ADRESS", based on which I want to create a col called "CompanyID". I want to add a suffix, which counts +1 for each row in a group of adresses, ideally starting from ".002". The output should look like this:

ADRESS	CompanyID
100000	100000.002
100000	100000.003
100000	100000.004
200000	100000.002
200000	100000.003
300000	100000.002

My idea was to declare a count variable:

DECLARE @count AS 
SET @count = '002'

And then use a while loop:

WHILE ()
BEGIN 
  SELECT ADRESS + '.' + @count AS CompanyID
  SET @count = @count +1 
END

Problem is, I don't have a idea what to loop through and also, which data type allows 3 digits without removing the first two zeros. I'm new to SQL so i would appreciate a short explanation.

Answer 1

Here is how you can do it:

• Use ROW_NUMBER() to get your ordered numbers
• +1 to start from 2
• Cast to varchar
• Add 000 in front
• Cut to last 3 characters - RIGHT()
• Add Address and '.' in front

---

 SELECT ADRESS 
, CAST(ADRESS AS VARCHAR(10)) 
  + '.' 
  + RIGHT('000' + CAST(1 + ROW_NUMBER() OVER (PARTITION BY ADRESS ORDER BY ADRESS) AS VARCHAR(3)),3) AS CompanyId
FROM Table1

db<>fiddle