MySQL query to get common or duplicate values from different columns

I have a table with two columns, one column (AffiliationCountry) shows the countries and the other column (ArtSubareaKeyword) shows the subject areas in related countries with comma-separated values.

I want to extract the subject area which is repeating for a country the same country one or more times and save it in a new column with the name “MostPopularSubjectArea”.

Table with values:

As you can see in the table that a country is repeating and its values are also repeating.

AffiliationCountry ArtSubareaKeyword1 ArtSubareaKeyword1 ArtSubareaKeyword1
Spain Cell membranes Cell staining Coimmunoprecipitation
Kazakhstan Factor analysis Human performance Immunofluorescence
Japan Bone marrow Diagnostic medicine Genetic loci
Kazakhstan Drug research Factor analysis Human performance

Results that are required:

I want a SQL query that can store for that country a new column that stores the common subjects area which is occurring more.

AffiliationCountry MostPopularSubjectArea
Kazakhstan Human performance

Answer

As per the table, you can select the pair of columns, union them and find the count using group by:

select 
t1.affiliation_country, t1.keyword, count(t1.keyword) as count_keyword
from
(
    select affiliation_country, lower(artsubareakeyword1) keyword from affliation_details
    union all
    select affiliation_country, lower(artsubareakeyword2) from affliation_details
    union all
    select affiliation_country, lower(artsubareakeyword3) from affliation_details
) t1
group by
t1.affiliation_country, t1.keyword
order by
count(t1.keyword) desc

Query Reference(Fiddle): https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=25bded45786a85f6740902699e633846

Updated Query:

with affiliation_details as
(
select 
t1.affiliation_country, t1.keyword, count(t1.keyword) as count_keyword
from
(
select affiliation_country, lower(artsubareakeyword1) keyword from affliation_details
union all
select affiliation_country, lower(artsubareakeyword2) from affliation_details
union all
select affiliation_country, lower(artsubareakeyword3) from affliation_details
) t1
group by
t1.affiliation_country, t1.keyword
order by
count(t1.keyword) desc
)

select 
  distinct affiliation_country
from
  affiliation_details
where
  count_keyword in (
    select 
      max(count_keyword) 
    from 
      affiliation_details
  )