Adding a count variable as a suffix in sql-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

Use the below query

SELECT Address, CAST(Address AS VARCHAR) '.' RIGHT('000' CAST(ROW_NUMBER() OVER(PARTITION BY Address ORDER BY Address) 1 AS VARCHAR),3) CompanyID FROM [dbo].[TblTestNum]

Change the "CAST(Address AS VARCHAR)" to '100000' if you want all row in 100000