How to make design data for handle stciks on countriy1 and country2 columns

Transact-SQL
,
1

I work on sql server 2017 i have table data as below

i need to make design for data to be best practise and prevent repeating data

my issue here on column countries1 and countries2 columns have data separated sticks so how to handle that

so are making design for countries table or what

my key is company and year and rev and not repeated

so How to handle sticks on column countries

 create table #countriesData
 (
 company int,
 [Year] int,
 rev  int,
 countries1 varchar(500),
 countries2 varchar(500)
 )
 insert into #countriesData(company,[Year],rev,countries1,countries2)
 values
 (12011,2010,121,'Egypt|France|America','India|France|America'),
 (12011,2011,121,'Egypt|Canda|America','India|Indonisya|America'),
 (12011,2012,121,'China|Canda|America','Pakistan|Indonisya|America'),
 (12099,2010,121,'SaudiArabia|France|America','Pakistan|sryia|America'),
 (12099,2011,121,'Egypt|Canda|German','Pakistan|Saudia|America'),
 (12099,2012,121, 'China|Italy|America','Holanda|Saudia|America')
2

You should take a look at 1:n tables. You have one key company and year and rev but multiple countries.

So I would create a table with an autoincrement for company and year and rev calles Data, and another table with that key and multiple entries, for each country 1.

Create table #Data
(
DataID int IDENTITY(1,1),
company int,
[Year] int,
rev int
);

Create table #Country
(
CountryID int IDENTITY(1,1),
Country
);

Create table #DataCountry
(
DataID INT,
CountryID int
);

You can easily split those countrys by using
STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs