I have a table with CountryName and an id.
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.
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?
You can try to use LEAD with window function get next
CountryName, then use
CASE WHEN to check the split by
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