How to add a distinct value to repeating columns in SQL Server?

user_10372502 0
Viewed 94

I have a table with CountryName and an id.

Eg.

id   CountryName 
-----------------
1    Afghanistan
2    Afghanistan
3    Afghanistan 
4    Albania     
5    Albania     
6    Albania     
7    Albania

I want to add a new column CountryID where all the repeating CountryName will have a distinct CountryID like below.

Eg.

id   CountryName   CountryID
-----------------------------    
1    Afghanistan      1
2    Afghanistan      1
3    Afghanistan      1
4    Albania          2
5    Albania          2
6    Albania          2
7    Albania          2

I have many countries that keep repeating and I want to add a CountryID to them to make it easier to do joins. Is it possible to do it by a T-Sql script?

ssms sql-server
2 Answers

Perhaps another option using DENSE_RANK():

Select id 
      ,CountryName
      ,CountryID  = dense_rank() over (order by CountryName)
From   YourTable
user_15700000

So, I understand that you want to add these CountryIDs into the table.
Hence, you would need an update statement to update the column CountryID.

Extending John Cappelletti's answer about using Dence_Rank, I think the following update statement should work. 

UPDATE t1 SET t1.CountryID = t2.CountryID
    FROM [Your table] t1
    JOIN (Select id 
          ,CountryName
          ,CountryID  = dense_rank() over (order by CountryName)
    From   [Your table]) t2 ON t1.CountryName = t2.CountryName

user_36355290
Related