CODEWITHSUNDEEP
sql-serverssms
Peter Parker
Peter Parker

Reputation: 31

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

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?

Upvotes: 2

Views: 98

Answers (2)

Sanketh. K. Jain
Sanketh. K. Jain

Reputation: 489

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

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 82010

Perhaps another option using DENSE_RANK():

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

Upvotes: 5

Related Questions