How to convert data separated pipe to rows?

Question

I work on sql server 2017 I have table name #countriesData data as below

my issue here How to convert countries column data separated pipe to rows .

meaning every rev and company and year will have only one country per row .

   create table #countriesData
 (
 company int,
 [Year] int,
 rev  int,
 countries varchar(500)
 )
 insert into #countriesData(company,[Year],rev,countries)
 values
 (12011,2010,121,'Egypt|France|America'),
 (12011,2011,121,'Egypt|Canda|America'),
 (12011,2012,121,'China|Canda|America'),
 (12099,2010,121,'SaudiArabia|France|America'),
 (12099,2011,121,'Egypt|Canda|German'),
 (12099,2012,121, 'China|Italy|America')

my expected result will be as below :

suppose i have two columns have pipe so how to modify query to handle two columns have pipe on same time

 create table #countriesData
 (
 company int,
 [Year] int,
 rev  int,
 countries varchar(500),
 countries1 varchar(500)
 )
 insert into #countriesData(company,[Year],rev,countries,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')

Answer 1

The problem here is that you have violated 1NF by storing multiple values in a single tuple. You are going to have to parse this into rows. You can use the built in STRING_SPLITfor this.

select *
from #countriesData c
cross apply STRING_SPLIT(c.countries, '|') x