How to add a distinct value to repeating columns in sql server?

2438 views sql-server
9

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 Country ID 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?

answered question

1 Answer

9

You can try to use LEAD with window function get next CountryName by CountryName, then use SUM with CASE WHEN to check the split by CountryName

SELECT id,
       CountryName,
       SUM(CASE WHEN nextVal IS NULL then 1 ELSE 0 END) OVER(ORDER BY CountryName) CountryID
FROM (
   select *,LEAD(CountryName)  OVER (PARTITION BY CountryName ORDER BY ID) nextVal
   from T 
) t1

sqlfiddle

posted this

Have an answer?

JD

Please login first before posting an answer.