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.
>Solution :
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